<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8" />
  <meta name="referrer" content="never" />
  
  
  <meta name="description" content="本人关于MySQL的笔记" />
  

  
  
  <meta name="google-site-verification" content="NCXVSqxqB-os803-VFMtIEd1SUNJVOIjctCfNYUwD0w" />
  <meta name="baidu-site-verification" content="Nzwx7HuplM" />
  
  <meta name="msvalidate.01" content="9E9EA92F7E5327E1FE8A5196D5253664" />
  
  
  
  <title>MySQL | masuo777的博客</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="本人关于MySQL的笔记">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL">
<meta property="og:url" content="https://masuo777.github.io/2021/06/01/Mysql/index.html">
<meta property="og:site_name" content="masuo777的博客">
<meta property="og:description" content="本人关于MySQL的笔记">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113142700.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143121.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143407.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143942.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113161138.png">
<meta property="og:image" content="g:/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/image/image-20220114104112179.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094651.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094707.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094952.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113161903.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114093616.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114095936.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114103438.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113170630.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220422171113.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113172205.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113172157.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114083113.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425170154.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425170137.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114083631.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114082655.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114100459.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114101113.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117174303.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117175023.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20210701230325.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114091027.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114091052.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114113256.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114105626.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114105956.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114112313.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114113023.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425100035.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220412111904.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220325105633.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220120154648.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114154335.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114152544.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114152201.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114155321.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114155433.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220121110741.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117100551.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117101604.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117102646.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111848.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117110920.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111206.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111838.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117112223.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117112406.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117123321.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117123619.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117172251.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117165543.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117170933.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118090647.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118094737.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118092826.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114111651.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114122552.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114112031.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114114751.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115103.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115149.png">
<meta property="og:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115319.png">
<meta property="article:published_time" content="2021-06-01T00:00:00.000Z">
<meta property="article:modified_time" content="2022-05-05T09:34:11.369Z">
<meta property="article:author" content="John Doe">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113142700.png">
  
    <link rel="alternative" href="/atom.xml" title="masuo777的博客" type="application/atom+xml">
  
  
    <link rel="icon" href="http://7u2hdm.com1.z0.glb.clouddn.com/favicon.ico">
  
  
<link rel="stylesheet" href="/css/style.css">

  

  
  <!-- baidu webmaster push -->
  <script src='//push.zhanzhang.baidu.com/push.js'></script>
<meta name="generator" content="Hexo 5.4.0"></head>
<body class="home blog custom-background custom-font-enabled single-author">
  <div id="page" class="hfeed site">
      <header id="masthead" class="site-header" role="banner">
    <hgroup>
      <h1 class="site-title">
        <a href="/" title="masuo777的博客" rel="home">masuo777的博客</a>
      </h1>
      
        <script type="text/javascript" src="http://api.hitokoto.us/rand?encode=js&charset=utf-8"></script>
        <h2 class="site-description"><script>hitokoto();</script></h2>
      
    </hgroup>

    <nav id="site-navigation" class="main-navigation" role="navigation">
            <button class="menu-toggle">菜单</button>
            <a class="assistive-text" href="/#content" title="跳至内容">跳至内容</a><!--TODO-->
            <div class="menu-main-container">
                <ul id="menu-main" class="nav-menu">
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/">MDS</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/todo">ToDo</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/images">images</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/about">ME</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/works">作品集</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="/tools">工具集</a></li>
                
                    <li class="menu-item menu-item-type-post_type menu-item-object-page"><a target="_blank" rel="noopener" href="https://github.com/Masuo777">my github</a></li>
                
                </ul>
            </div>
    </nav>
</header>
      <div id="main" class="wrapper">
        <div id="primary" class="site-content"><div id="content" role="main"><article id="post-Mysql" class="post-Mysql post type-post status-publish format-standard hentry">
    <!---->

      <header class="entry-header">
        
        
  
    <h1 class="entry-title article-title">
      MySQL
    </h1>
  

        
        <div class="comments-link">
            
            <a href="/2021/06/01/Mysql/#comments" class="leave-reply">评论</a>
            
            <a href="javascript:void(0);" data-url="https://masuo777.github.io/2021/06/01/Mysql/" data-id="cl3jit8nn000e2ojbat7fdsyy" class="leave-reply bdsharebuttonbox" data-cmd="more">分享</a>
        </div><!-- .comments-link -->
      </header><!-- .entry-header -->

    <div class="entry-content">
      
        <h1 id="MySQL基础"><a href="#MySQL基础" class="headerlink" title="MySQL基础"></a>MySQL基础</h1><blockquote>
<p>MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。</p>
</blockquote>
<span id="more"></span>

<h2 id="安装"><a href="#安装" class="headerlink" title="安装"></a>安装</h2><h3 id="Docker"><a href="#Docker" class="headerlink" title="Docker"></a>Docker</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 首先拉取MySQL镜像 https://hub.docker.com/_/mysql?tab=tags</span></span><br><span class="line"><span class="comment"># 如果我们需要的是MySQL5.7，那么找到tag=5.7的</span></span><br><span class="line">sudo docker pull mysql:5.7</span><br><span class="line"></span><br><span class="line"><span class="comment"># 有了镜像之后，创建容器</span></span><br><span class="line">sudo docker run --name mysql-5.7-1 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7</span><br><span class="line"></span><br><span class="line"><span class="comment"># 进入MySQL容器的交互模式</span></span><br><span class="line">sudo docker <span class="built_in">exec</span> -it mysql-5.7-1 /bin/bash</span><br><span class="line"></span><br><span class="line"><span class="comment"># 连接数据库</span></span><br><span class="line">mysql -u root -p</span><br><span class="line"><span class="comment"># 输入密码就可以</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 输入quit退出</span></span><br><span class="line">quit</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113142700.png" alt="image-20220113142659427"></p>
<h3 id="Linux"><a href="#Linux" class="headerlink" title="Linux"></a>Linux</h3><h3 id="Windows"><a href="#Windows" class="headerlink" title="Windows"></a>Windows</h3><h2 id="用户"><a href="#用户" class="headerlink" title="用户"></a>用户</h2><blockquote>
<p>为了数据的安全，我们在访问数据库的时候，不能直接以root的用户进行操作，所以我们需要创建新用户，并给他们相应的权限去完成相应的任务。</p>
</blockquote>
<h3 id="创建用户"><a href="#创建用户" class="headerlink" title="创建用户"></a>创建用户</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 创建访问用户</span><br><span class="line"># CREATE USER &#x27;用户名&#x27;@&#x27;授权ip&#x27; identified by &#x27;用户密码&#x27;</span><br><span class="line">CREATE USER &#x27;query_1&#x27;@&#x27;%&#x27; identified by &#x27;query_pwd&#x27;</span><br></pre></td></tr></table></figure>

<ul>
<li>query_1：用户名</li>
<li>@：拼接符，拼接用户名和主机地址</li>
<li>%：来自任何地址的访问</li>
<li>query_pwd：用户密码</li>
</ul>
<h3 id="权限管理"><a href="#权限管理" class="headerlink" title="权限管理"></a>权限管理</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 给</span><br><span class="line">grant all on *.* to &#x27;query_1&#x27;@&#x27;%&#x27;</span><br></pre></td></tr></table></figure>

<ul>
<li>all：所有的权限</li>
<li>on：基于哪些表，后面跟数据的数据表</li>
<li><code>*.*</code> 所有数据库下的所有表，第一个<code>*</code>代表数据库</li>
</ul>
<h2 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h2><h3 id="创建数据库"><a href="#创建数据库" class="headerlink" title="创建数据库"></a>创建数据库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> DATABASE <span class="operator">&lt;</span>数据库名称<span class="operator">&gt;</span>;</span><br><span class="line"># <span class="keyword">or</span></span><br><span class="line">MYSQLADMIN <span class="operator">-</span>u root <span class="operator">-</span>p <span class="keyword">CREATE</span> DATABASE <span class="operator">&lt;</span>数据库名称<span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>CREATE是在数据库中创建数据库,需要结束符 <code>;</code></p>
<p>MYSQLADMIN是在外部通过该命令创建数据库,不需要结束符。</p>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143121.png" alt="image-20220113143119758"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143407.png" alt="image-20220113143406126"></p>
<h3 id="查看数据库"><a href="#查看数据库" class="headerlink" title="查看数据库"></a>查看数据库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 查看所有的数据库</span><br><span class="line"><span class="keyword">SHOW</span> DATABASES;</span><br><span class="line"></span><br><span class="line"># 查看当前数据库</span><br><span class="line"><span class="keyword">SELECT</span> DATABASE();</span><br></pre></td></tr></table></figure>

<h3 id="删除数据库"><a href="#删除数据库" class="headerlink" title="删除数据库"></a>删除数据库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> DATABASE <span class="operator">&lt;</span>数据库名称<span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113143942.png" alt="image-20220113143941784"></p>
<h3 id="选择数据库"><a href="#选择数据库" class="headerlink" title="选择数据库"></a>选择数据库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">USE <span class="operator">&lt;</span>数据库名称<span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>

<hr>
<h2 id="数据类型"><a href="#数据类型" class="headerlink" title="数据类型"></a>数据类型</h2><blockquote>
<p>MySQL中定义的数据字段的类型对优化数据库非常重要。</p>
<p>MySQL支持多种数据类型，大致可分为三种</p>
<ul>
<li>数值</li>
<li>日期</li>
<li>字符串</li>
</ul>
</blockquote>
<h3 id="数值型"><a href="#数值型" class="headerlink" title="数值型"></a>数值型</h3><blockquote>
<p>MySQL支持所有标准SQL数值数据类型。</p>
<p><strong>严格数值数据类型</strong>：INTEGER、SMALLINT、DECIMAL、NUMERIC</p>
<p><strong>近似数值数据类型</strong>：FLOAT、REAL、DOUBLE PRECISION</p>
<p>也支持整数类型 TINYINT、MEDIUMINT 、 BIGINT</p>
<p>1 Byte = 1 byte = 1B = 1字节 = 8 bit = 8位</p>
<p>1KB = 1024B</p>
<p>1 字符 = 多个字节</p>
<p>在UTF8中，一个英文字符占一个字节，一个汉字占三个字节。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">类型</th>
<th align="left">大小</th>
<th align="left">范围（有符号）</th>
<th align="left">范围（无符号）</th>
<th align="left">用途</th>
</tr>
</thead>
<tbody><tr>
<td align="left">TINYINT</td>
<td align="left">1 Bytes<br />1字节</td>
<td align="left">(-128，127)</td>
<td align="left">(0，255)</td>
<td align="left">小整数值</td>
</tr>
<tr>
<td align="left">SMALLINT</td>
<td align="left">2 Bytes<br />2字节</td>
<td align="left">(-32 768，32 767)</td>
<td align="left">(0，65 535)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">MEDIUMINT</td>
<td align="left">3 Bytes<br />3字节</td>
<td align="left">(-8 388 608，8 388 607)</td>
<td align="left">(0，16 777 215)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">INT或INTEGER</td>
<td align="left">4 Bytes<br />4字节</td>
<td align="left">(-2 147 483 648，2 147 483 647)</td>
<td align="left">(0，4 294 967 295)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">BIGINT</td>
<td align="left">8 Bytes<br />8字节</td>
<td align="left">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td>
<td align="left">(0，18 446 744 073 709 551 615)</td>
<td align="left">极大整数值</td>
</tr>
<tr>
<td align="left">FLOAT</td>
<td align="left">4 Bytes<br />4字节</td>
<td align="left">(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td>
<td align="left">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td>
<td align="left">单精度 浮点数值</td>
</tr>
<tr>
<td align="left">DOUBLE</td>
<td align="left">8 Bytes<br />8字节</td>
<td align="left">(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td align="left">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td align="left">双精度 浮点数值</td>
</tr>
<tr>
<td align="left">DECIMAL</td>
<td align="left">对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td>
<td align="left">依赖于M和D的值</td>
<td align="left">依赖于M和D的值</td>
<td align="left">小数值</td>
</tr>
</tbody></table>
<h3 id="日期型"><a href="#日期型" class="headerlink" title="日期型"></a>日期型</h3><blockquote>
<p>表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。</p>
<p>每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。</p>
<p>TIMESTAMP类型有专有的自动更新特性，将在后面描述。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">类型</th>
<th align="left">大小 ( bytes)</th>
<th align="left">范围</th>
<th align="left">格式</th>
<th align="left">用途</th>
</tr>
</thead>
<tbody><tr>
<td align="left">DATE</td>
<td align="left">3</td>
<td align="left">1000-01-01/9999-12-31</td>
<td align="left">YYYY-MM-DD</td>
<td align="left">日期值</td>
</tr>
<tr>
<td align="left">TIME</td>
<td align="left">3</td>
<td align="left">‘-838:59:59’/‘838:59:59’</td>
<td align="left">HH:MM:SS</td>
<td align="left">时间值或持续时间</td>
</tr>
<tr>
<td align="left">YEAR</td>
<td align="left">1</td>
<td align="left">1901/2155</td>
<td align="left">YYYY</td>
<td align="left">年份值</td>
</tr>
<tr>
<td align="left">DATETIME</td>
<td align="left">8</td>
<td align="left">1000-01-01 00:00:00/9999-12-31 23:59:59</td>
<td align="left">YYYY-MM-DD HH:MM:SS</td>
<td align="left">混合日期和时间值</td>
</tr>
<tr>
<td align="left">TIMESTAMP</td>
<td align="left">4</td>
<td align="left">1970-01-01 00:00:00结束时间是第 <strong>2147483647</strong> 秒，北京时间 <strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</td>
<td align="left">YYYYMMDD HHMMSS</td>
<td align="left">混合日期和时间值，时间戳</td>
</tr>
</tbody></table>
<h4 id="DATE"><a href="#DATE" class="headerlink" title="DATE"></a>DATE</h4><blockquote>
<p>DATE类型不支持函数化的默认初始值，其默认值必须是一个常量。</p>
<p>DATETIME，支持NOW()、CURRENT_TIMESTAMP</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> db_time(</span><br><span class="line">`date_init_1` <span class="type">date</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_DATE</span>,<span class="operator">/</span><span class="operator">/</span><span class="number">1</span></span><br><span class="line">`date_init_2` <span class="type">date</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIME</span>,</span><br><span class="line">`date_init_3` <span class="type">date</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span>,</span><br><span class="line">`datetime_init_1` datetime <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_DATE</span>,</span><br><span class="line">`datetime_init_2` datetime <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIME</span>,</span><br><span class="line">`datetime_init_2` datetime <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span>,<span class="operator">/</span><span class="operator">/</span><span class="number">1</span> 等价于使用 now()</span><br><span class="line"> # 在使用<span class="type">timestamp</span>时，默认值已经设为了<span class="built_in">CURRENT_TIMESTAMP</span></span><br><span class="line">`timestamp_init_1` <span class="type">timestamp</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_DATE</span>,</span><br><span class="line">`timestamp_init_2` <span class="type">timestamp</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIME</span>,</span><br><span class="line">`timestamp_init_3` <span class="type">timestamp</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span>;<span class="operator">/</span></span><br></pre></td></tr></table></figure>



<h3 id="字符串"><a href="#字符串" class="headerlink" title="字符串"></a>字符串</h3><blockquote>
<p>也称文本类,无特殊说明存储的都是字符.</p>
<p>字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。</p>
<p>char(n) 和 varchar(n) 中括号中 n 代表<strong>字符</strong>的个数，并不代表<strong>字节</strong>个数，比如 CHAR(30) 就可以存储 30 个字符。</p>
<p>CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。</p>
<p>BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。</p>
<p>BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。</p>
<p>有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">数据类型</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">CHAR(size)</td>
<td align="left">保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。</td>
</tr>
<tr>
<td align="left">VARCHAR(size)</td>
<td align="left">保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。</td>
</tr>
<tr>
<td align="left">TINYTEXT</td>
<td align="left">存放最大长度为 255 个字符的字符串。</td>
</tr>
<tr>
<td align="left">TEXT</td>
<td align="left">存放最大长度为 65,535 个字符的字符串。</td>
</tr>
<tr>
<td align="left">BLOB</td>
<td align="left">用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。</td>
</tr>
<tr>
<td align="left">MEDIUMTEXT</td>
<td align="left">存放最大长度为 16,777,215 个字符的字符串。</td>
</tr>
<tr>
<td align="left">MEDIUMBLOB</td>
<td align="left">用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。</td>
</tr>
<tr>
<td align="left">LONGTEXT</td>
<td align="left">存放最大长度为 4,294,967,295 个字符的字符串。</td>
</tr>
<tr>
<td align="left">LONGBLOB</td>
<td align="left">用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。</td>
</tr>
<tr>
<td align="left">ENUM(x,y,z,etc.)</td>
<td align="left">允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X’,’Y’,’Z’)</td>
</tr>
<tr>
<td align="left">SET</td>
<td align="left">与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。</td>
</tr>
</tbody></table>
<h3 id="NULL"><a href="#NULL" class="headerlink" title="NULL"></a>NULL</h3><blockquote>
<p>数据库对于<code>null</code>有额外的处理方式，因为一般的命令对NULL可能不在生效。</p>
</blockquote>
<ul>
<li>IS NULL：当列值为<code>null</code>时，会返回true</li>
<li>IS NOT NULL：当列值不为<code>null</code>时，会返回true</li>
<li>&lt;=&gt;：当比较的两个值相等或都为<code>null</code>时，返回true</li>
</ul>
<hr>
<h2 id="数据表"><a href="#数据表" class="headerlink" title="数据表"></a>数据表</h2><h3 id="三大范式"><a href="#三大范式" class="headerlink" title="三大范式"></a>三大范式</h3><p>参考：<a target="_blank" rel="noopener" href="https://www.jb51.net/article/223167.htm">https://www.jb51.net/article/223167.htm</a></p>
<ul>
<li><p><strong>第一范式</strong></p>
<blockquote>
<p>列不可再分,确保列的<strong>原子性</strong>.</p>
</blockquote>
</li>
<li><p><strong>第二范式</strong></p>
<blockquote>
<p>base on 1NF</p>
<p>每一列都和主键相关,而不只针对主键的某一部分相关,主要针对联合主键.</p>
<p>通俗讲即是：有主键，非主键依赖主键</p>
</blockquote>
  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br></pre></td><td class="code"><pre><span class="line">    ```</span><br><span class="line"></span><br><span class="line">    </span><br><span class="line"></span><br><span class="line"><span class="operator">-</span> <span class="operator">*</span><span class="operator">*</span>第三范式<span class="operator">*</span><span class="operator">*</span></span><br><span class="line"></span><br><span class="line">    <span class="operator">&gt;</span> Base <span class="keyword">on</span> <span class="number">2</span>NF</span><br><span class="line">    <span class="operator">&gt;</span></span><br><span class="line">    <span class="operator">&gt;</span> 每一列数据都和主键直接相关,而不是间接相关.</span><br><span class="line">    <span class="operator">&gt;</span></span><br><span class="line">    <span class="operator">&gt;</span> 通俗讲即是：非主键字段不能互相依赖。</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">### 创建表</span><br><span class="line"></span><br><span class="line"><span class="operator">&gt;</span>  创建MySQL数据表需要以下信息：</span><br><span class="line"><span class="operator">&gt;</span></span><br><span class="line"><span class="operator">&gt;</span> <span class="operator">-</span> 表名</span><br><span class="line"><span class="operator">&gt;</span> <span class="operator">-</span> 表字段名</span><br><span class="line"><span class="operator">&gt;</span> <span class="operator">-</span> 定义每个表字段</span><br><span class="line"></span><br><span class="line">```<span class="keyword">sql</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> </span><br><span class="line">[IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] TABLE_NAME </span><br><span class="line">(</span><br><span class="line">    colum_name colum_type</span><br><span class="line">    	[</span><br><span class="line">            AUTO_INCREMENT, 		  # 自增</span><br><span class="line">            <span class="keyword">PRIMARY</span> KEY,			  # 主键</span><br><span class="line">            <span class="keyword">UNIQUE</span>,					  # 唯一键</span><br><span class="line">            COMMENT <span class="string">&#x27;注释&#x27;</span>,	   		 # 注释</span><br><span class="line">            <span class="keyword">DEFAULT</span> default_value,	  # 默认值</span><br><span class="line">            <span class="keyword">NOT</span> <span class="keyword">NULL</span>,				  # 非空</span><br><span class="line">        ],</span><br><span class="line">    colum_name colum_type,</span><br><span class="line">    colum_name colum_type</span><br><span class="line">)</span><br><span class="line">[</span><br><span class="line">    ENGINE<span class="operator">=</span>InnoDB<span class="operator">|</span>MyISAM,</span><br><span class="line">    CHARSET<span class="operator">=</span>UTF8</span><br><span class="line">];</span><br></pre></td></tr></table></figure></li>
</ul>
<p><strong>表结构设计</strong></p>
<ul>
<li>自增列推荐使用<code>BIGINT</code>类型</li>
<li>字段在满足业务要求的情况下尽量选择占字节少的类型</li>
<li>IP类型字段推荐使用int类型</li>
<li>业务活跃的表须有行数据的创建时间和修改时间</li>
<li>表中字段尽量是NOT NULL</li>
<li>字符集数据库、数据表尽量选utf8</li>
<li>不推荐使用外键</li>
<li>单表数据尽量不要超过5000万行或数据量不大于30G（SSD硬盘）</li>
<li>单表数据尽量不要超过2000万行或数据量不大于15G（SAS硬盘）</li>
</ul>
<p>举个:chestnut:s</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"># 学生表，学号，姓名，年龄，班级，出生年月，</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> `table_student`(</span><br><span class="line">    `id` <span class="type">int</span>(<span class="number">8</span>) UNSIGNED AUTO_INCREMENT，</span><br><span class="line">    `stu_num` <span class="type">int</span>(<span class="number">8</span>),</span><br><span class="line">    `stu_name` <span class="type">varchar</span>(<span class="number">16</span>),</span><br><span class="line">    `stu_age` <span class="type">int</span>(<span class="number">3</span>),</span><br><span class="line">    `stu_class` <span class="type">varchar</span>(<span class="number">10</span>),</span><br><span class="line">    `stu_birth` <span class="type">date</span>,</span><br><span class="line">    <span class="keyword">PRIMARY</span> KEY (`id`)<span class="operator">/</span><span class="operator">/</span>设置主键为id</span><br><span class="line">)ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>UTF8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"># 学分表,带外键stu_id</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `table_score` (</span><br><span class="line">    `id` <span class="type">int</span>(<span class="number">8</span>),</span><br><span class="line">    `score` mediumint(<span class="number">4</span>) ,</span><br><span class="line">    `stu_id` <span class="type">int</span>(<span class="number">8</span>) unsigned,<span class="operator">/</span><span class="operator">/</span>这里必须跟学生表的主键一致</span><br><span class="line">    <span class="keyword">PRIMARY</span> KEY (`id`),</span><br><span class="line">    <span class="keyword">FOREIGN</span> KEY (`stu_id`) <span class="keyword">REFERENCES</span> `table_student` (`id`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>UTF8;</span><br><span class="line"></span><br><span class="line"># 不存在才创建表，且需要带自增主键，唯一键，数值默认为<span class="number">0</span>，时间默认当前时间按戳，且带注释</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> if <span class="keyword">not</span> <span class="keyword">exists</span> `user_info_vip`(</span><br><span class="line">    `id` <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="keyword">null</span> auto_increment <span class="keyword">primary</span> key comment <span class="string">&#x27;自增ID&#x27;</span>,</span><br><span class="line">    `uid` <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="keyword">null</span> <span class="keyword">unique</span> comment <span class="string">&#x27;用户ID&#x27;</span>,</span><br><span class="line">    `nick_name` <span class="type">varchar</span>(<span class="number">64</span>) comment <span class="string">&#x27;昵称&#x27;</span>,</span><br><span class="line">    `achievement`	<span class="type">int</span>(<span class="number">11</span>) <span class="keyword">default</span> <span class="number">0</span> comment <span class="string">&#x27;成就值&#x27;</span>,# 数值默认<span class="number">0</span></span><br><span class="line">    `level`	<span class="type">int</span>(<span class="number">11</span>) comment <span class="string">&#x27;用户等级&#x27;</span>,</span><br><span class="line">    `job` <span class="type">varchar</span>(<span class="number">32</span>) comment <span class="string">&#x27;职业方向&#x27;</span>,</span><br><span class="line">    `register_time`	DATETIME <span class="keyword">default</span> <span class="built_in">current_timestamp</span> comment <span class="string">&#x27;注册时间&#x27;</span> # 时间默认当前时间戳</span><br><span class="line">)ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>UTF8;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>IF NOT EXISTS：不存在数据库才会创建数据库</p>
<p>UNSIGNED：无符号</p>
<p>AUTO_INCREMENT：自增，一般用在主键，数值自动+1</p>
<p>PRIMARY KEY：声明主键</p>
<p>ENGINE：数据库引擎</p>
<p>CHARSET：数据库编码</p>
</blockquote>
<img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113161138.png" alt="image-20220113161137290" style="zoom:150%;" />

<img src="G:/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/image/image-20220114104112179.png" alt="image-20220114104112179" style="zoom:150%;" />

<h3 id="查看表"><a href="#查看表" class="headerlink" title="查看表"></a>查看表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># 查看当前数据库的所有表</span><br><span class="line"><span class="keyword">SHOW</span> TABLES;</span><br><span class="line"></span><br><span class="line"># 查看当前表结构</span><br><span class="line"><span class="keyword">DESC</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span>;</span><br><span class="line"></span><br><span class="line"># 查看表信息</span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">TABLE</span> STATUS;</span><br><span class="line"></span><br><span class="line"># 查看表信息,高可读模式</span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">TABLE</span> STATUS <span class="keyword">LIKE</span> <span class="string">&#x27;table_name&#x27;</span>\G;</span><br><span class="line"></span><br><span class="line"># 查看建表语句</span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094651.png" alt="image-20220114094650086"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094707.png" alt="image-20220114094706596"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114094952.png" alt="image-20220114094951129"></p>
<h3 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>数据表名称<span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>



<img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113161903.png" alt="image-20220113161901821" style="zoom: 150%;" />



<h3 id="修改表"><a href="#修改表" class="headerlink" title="修改表"></a>修改表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"># 修改表名,旧的表明可以不写</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> RENAME <span class="keyword">TO</span> <span class="operator">&lt;</span>new_name<span class="operator">&gt;</span></span><br><span class="line"># <span class="keyword">or</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> RENAME <span class="keyword">AS</span> <span class="operator">&lt;</span>new_name<span class="operator">&gt;</span></span><br><span class="line"></span><br><span class="line"># 添加列</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">ADD</span> <span class="keyword">COLUMN</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span> <span class="operator">&lt;</span>colum_type<span class="operator">&gt;</span></span><br><span class="line"></span><br><span class="line"># 删除列</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">DROP</span> <span class="keyword">COLUMN</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span></span><br><span class="line"></span><br><span class="line"># 修改列,在MySQL中修改列属性需要使用modify而不是<span class="keyword">alter</span></span><br><span class="line"></span><br><span class="line"># 修改列属性，可选参数：给列增加唯一属性 <span class="keyword">UNIQUE</span>，</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> MODIFY <span class="keyword">COLUMN</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="operator">&lt;</span>data_type<span class="operator">&gt;</span> [<span class="keyword">UNIQUE</span>]</span><br><span class="line"></span><br><span class="line"># 修改列名称以及列属性 可选参数：<span class="keyword">NOT</span> <span class="keyword">NULL</span><span class="operator">|</span>AUTO_INCREMENT<span class="operator">|</span><span class="keyword">UNIQUE</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> </span><br><span class="line">CHANGE <span class="keyword">COLUMN</span> <span class="operator">&lt;</span>old_column_name<span class="operator">&gt;</span> <span class="operator">&lt;</span>new_column_name<span class="operator">&gt;</span> <span class="operator">&lt;</span>data_type<span class="operator">&gt;</span> [<span class="keyword">NOT</span> <span class="keyword">NULL</span><span class="operator">|</span>AUTO_INCREMENT<span class="operator">|</span><span class="keyword">UNIQUE</span>]</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114093616.png" alt="image-20220114093615435"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114095936.png" alt="image-20220114095935567"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114103438.png" alt="image-20220114103437984"></p>
<h3 id="通配符"><a href="#通配符" class="headerlink" title="通配符"></a>通配符</h3><p>在搜索数据库中的数据时，<code>SQL</code> 通配符可以替代一个或多个字符。</p>
<p><code>SQL</code> 通配符必须与 LIKE 运算符一起使用。</p>
<p>在 <code>SQL</code> 中，可使用以下通配符,但<code>MySQL</code>不一定包含下面这些.</p>
<table>
<thead>
<tr>
<th align="left">通配符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">%</td>
<td align="left">代表零个或多个字符</td>
</tr>
<tr>
<td align="left">_</td>
<td align="left">仅替代一个字符</td>
</tr>
<tr>
<td align="left">[ charlist ]</td>
<td align="left">字符列中的任何单一字符</td>
</tr>
<tr>
<td align="left">[^charlist]或者[!charlist]</td>
<td align="left">不在（charlist需要被你的条件替换）</td>
</tr>
</tbody></table>
<h3 id="CRUD"><a href="#CRUD" class="headerlink" title="CRUD"></a>CRUD</h3><blockquote>
<p>CRUD就是我们所说的增删改查，也是数据库入门必备。</p>
</blockquote>
<h4 id="INSERT"><a href="#INSERT" class="headerlink" title="INSERT"></a>INSERT</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"># 普通插入，不需要指定列名，此时需要列与数据的类型和数量全匹配</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name <span class="keyword">VALUE</span> (value1, value2, ..., valueN)</span><br><span class="line"></span><br><span class="line"># 等同于，<span class="keyword">VALUE</span> <span class="operator">&lt;=</span><span class="operator">=</span><span class="operator">&gt;</span> <span class="keyword">VALUES</span>，只是<span class="keyword">VALUES</span>效率更高，你可以试试</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name <span class="keyword">VALUES</span> (value1, value2, ..., valueN)</span><br><span class="line"></span><br><span class="line"># 指定字段插入I，</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name (field1, field2, ..., fieldN) </span><br><span class="line"> <span class="keyword">VALUES</span> (value1, value2, ..., valueN)</span><br><span class="line"> </span><br><span class="line"># 指定字段插入II</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name <span class="keyword">SET</span> field1<span class="operator">=</span>value1,field2<span class="operator">=</span>value2,...fieldN<span class="operator">=</span>valueN;</span><br><span class="line"></span><br><span class="line"># 一次插入多条数据，尽量指定需要插入的列明</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name (field1, field2, ..., fieldN)</span><br><span class="line"><span class="keyword">VALUES</span> (value1, value2, ..., valueN)，(value1, value2, ..., valueN)，(value1, value2, ..., valueN)...</span><br></pre></td></tr></table></figure>

<blockquote>
<p>主键自增是不用主动插入数据的，但是再插入时不能指定自增的id主键，不然会报不匹配。</p>
</blockquote>
<p>进阶用法</p>
<ul>
<li>表明使用``包裹</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 将表名用 `` 符号包起来</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `table_name` () <span class="keyword">VALUES</span> ()</span><br></pre></td></tr></table></figure>

<p>这样当我们<strong>表名</strong>（table name）较复杂时也不会出现因<strong>表名</strong>而引起的错误</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113170630.png" alt="image-20220113170629667"></p>
<ul>
<li>导入历史数据</li>
</ul>
<blockquote>
<p>在实际开发过程中，会产生很多历史数据，且很多历史数据都不会经常用到，此时如果不对历史数据做处理，我们的数据库会越来越大，性能会越来越低，所以我们需要将一部分历史数据导出。</p>
<p>假设有表exam_record，数据如下</p>
<p>id    uid      eid        create_date                    last_date                       score</p>
<p>12    111    1111    2020-01-02 09:21:01    2020-01-02 09:21:01    10<br>13    111    1111    2020-01-02 09:21:01    2020-01-02 09:21:01    10<br>14    111    1111    2020-01-02 09:21:01    2020-01-02 09:21:01    10<br>15    111    1111    2020-01-02 09:21:01    2020-01-02 09:21:01    10<br>16    111    1111    2020-01-02 09:21:01    2020-01-02 09:21:01    10<br>4    1002    9001    2021-05-02 10:01:01    2021-05-02 10:30:01    81<br>5    1002    9002    2021-09-02 12:01:01<br>3    1002    9003    2022-04-22 16:56:58    2022-04-22 16:56:58    50</p>
</blockquote>
<p>现在将创建日期在2021年及之前的数据放到history_exam_record表中。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 将2021年以前的数据导入历史表，</span><br><span class="line">INSERT INTO history_exam_record (uid, eid, create_date, last_date, score)</span><br><span class="line">SELECT uid, eid, create_date, last_date, score</span><br><span class="line">FROM exam_record WHERE YEAR(create_date) &lt;= 2021</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220422171113.png" alt="image-20220422171113796"></p>
<ul>
<li>带更新的插入</li>
</ul>
<blockquote>
<p>我们在插入时，有时会遇到id主键相同的插入，一般情况下，我们会重新声明一个不一样的id主键，但是有时候，需要我们覆盖原数据，并插入新数据，此时我们就需要使用<code>REPLACE</code></p>
</blockquote>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">REPLACE INTO table_name (field1, field2, ..., fieldN)</span><br><span class="line">VALUES (value1, value2, ..., valueN)</span><br></pre></td></tr></table></figure>





<h4 id="SELECT"><a href="#SELECT" class="headerlink" title="SELECT"></a>SELECT</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span>,<span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span>...</span><br><span class="line"><span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line">[<span class="keyword">WHERE</span> ]</span><br><span class="line">[LIMIT ][<span class="keyword">OFFSET</span> ]</span><br></pre></td></tr></table></figure>

<blockquote>
<p><code>WHERE</code>：可选参数，设置筛选条件，详情可查看 [WHERE](# WHERE)。</p>
<p><code>LIMIT</code>：可选参数，限制筛选数目，详情可看 [LIMIT](# LIMIT)。</p>
<ul>
<li>一个参数N：表示从0开始的N个符合条件的数据</li>
<li>两个参数N，M：表示从N开始的M个符合条件的数据</li>
</ul>
<p><code>OFFSET</code>：可选参数，偏移量，配合<code>LIMIT</code>使用</p>
</blockquote>
<h4 id="DELETE"><a href="#DELETE" class="headerlink" title="DELETE"></a>DELETE</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> [<span class="keyword">WHERE</span> ]</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>如果没有指定 <code>WHERE</code> 子句，<code>MySQL</code> 表中的所有记录将被删除。</li>
<li>你可以在 <code>WHERE</code> 子句中指定任何条件，删除符合指定条件的数据。</li>
<li>您可以在单个表中一次性删除记录。</li>
</ul>
</blockquote>
<h4 id="UPDATE"><a href="#UPDATE" class="headerlink" title="UPDATE"></a>UPDATE</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">UPDATE <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">SET</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;=</span><span class="operator">&lt;</span>new_value<span class="operator">&gt;</span>,<span class="operator">&lt;</span>colum_name<span class="operator">&gt;=</span><span class="operator">&lt;</span>new_value<span class="operator">&gt;</span>... [<span class="keyword">WHERE</span>] </span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>你可以同时更新一个或多个字段。</li>
<li>你可以在 <code>WHERE</code> 子句中指定任何条件。</li>
<li>你可以在一个单独表中同时更新数据。</li>
</ul>
</blockquote>
<h4 id="TRUNCATE"><a href="#TRUNCATE" class="headerlink" title="TRUNCATE"></a>TRUNCATE</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">TRUNCATE [TABLE] &lt;table_name&gt;</span><br></pre></td></tr></table></figure>

<p>我们都知道，删除表需要使用 <code>DELETE</code>语法，但是如果需要我们删除表中所有数据的同时还要重置自增主键的话，<code>DELETE</code>已经力不从心了，虽然他能删除表中所有数据，但是他不能重置自增主键，此时我们就需要引入<code>TRUNCATE</code>，他专门用于重置表，且速度比<code>DELETE</code>快。</p>
<h4 id="LIMIT"><a href="#LIMIT" class="headerlink" title="LIMIT"></a>LIMIT</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">LIMIT [offset,] counts</span><br></pre></td></tr></table></figure>

<ul>
<li>offset：起始位置，即偏移量，如<code>offset=1</code>，那么就从查询的结果中第1位开始取，可选参数</li>
<li>counts：记录数，</li>
</ul>
<p>上面两个参数只接受正整数，且不支持运算。</p>
<p>当使用一个参数<strong>N</strong>时，会返回从<strong>0</strong>到<strong>N</strong>之间的所有行数据，如</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from table_name limit 10;</span><br></pre></td></tr></table></figure>

<p>此时数据库会返回前十条数据，如果数据库中的数据量小于十条则返回所有行数据。</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113172205.png" alt="image-20220113172204687"></p>
<p>当使用两个参数<strong>N</strong>，<strong>M</strong>时，会返回从<strong>N</strong>开始的<strong>M</strong>行数据，如</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from table_name limit 0,10;</span><br></pre></td></tr></table></figure>

<p>此时会返回从0开始的10条数据，不足则全部返回。</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220113172157.png" alt="image-20220113172156128"></p>
<h4 id="WHERE"><a href="#WHERE" class="headerlink" title="WHERE"></a>WHERE</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">WHERE</span> <span class="operator">&lt;</span>条件<span class="number">1</span><span class="operator">&gt;</span> <span class="keyword">AND</span><span class="operator">/</span><span class="keyword">OR</span> <span class="operator">&lt;</span>条件<span class="number">2</span><span class="operator">&gt;</span> <span class="keyword">AND</span><span class="operator">/</span><span class="keyword">OR</span> <span class="operator">&lt;</span>条件<span class="number">3</span><span class="operator">&gt;</span> ...</span><br></pre></td></tr></table></figure>

<blockquote>
<p><code>WHERE</code> 子句可拼接到其他语句中，但不建议语句过于复杂。</p>
<ul>
<li>查询语句中你可以使用一个或者多个表，表之间使用逗号**,** 分割，并使用<code>WHERE</code>语句来设定查询条件。</li>
<li>你可以在 <code>WHERE</code> 子句中指定任何条件。</li>
<li>你可以使用 <code>AND</code> 或者 <code>OR</code> 指定一个或多个条件。</li>
<li><code>WHERE</code> 子句也可以运用于 <code>SQL</code> 的 <code>DELETE</code> 或者 <code>UPDATE</code> 命令。</li>
<li><code>WHERE</code> 子句类似于程序语言中的 <code>if</code> 条件，根据 <code>MySQL</code> 表中的字段值来读取指定的数据。</li>
</ul>
<p><code>MySQL</code> 的 <code>WHERE</code> 子句的字符串比较是不区分大小写的。 你可以使用 <code>BINARY</code> 关键字来设定 <code>WHERE</code> 子句的字符串比较是区分大小写的。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">操作符</th>
<th align="left">描述</th>
<th align="left">实例</th>
</tr>
</thead>
<tbody><tr>
<td align="left">=</td>
<td align="left">等号，检测两个值是否相等，如果相等返回true</td>
<td align="left">(A = B) 返回false。</td>
</tr>
<tr>
<td align="left">&lt;&gt; !=</td>
<td align="left">不等于，检测两个值是否相等，如果不相等返回true</td>
<td align="left">(A != B) 返回 true。</td>
</tr>
<tr>
<td align="left">&gt;</td>
<td align="left">大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true</td>
<td align="left">(A &gt; B) 返回false。</td>
</tr>
<tr>
<td align="left">&lt;</td>
<td align="left">小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true</td>
<td align="left">(A &lt; B) 返回 true。</td>
</tr>
<tr>
<td align="left">&gt;=</td>
<td align="left">大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true</td>
<td align="left">(A &gt;= B) 返回false。</td>
</tr>
<tr>
<td align="left">&lt;=</td>
<td align="left">小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true</td>
<td align="left">(A &lt;= B) 返回 true。</td>
</tr>
</tbody></table>
<h4 id="DISTINCT"><a href="#DISTINCT" class="headerlink" title="DISTINCT"></a>DISTINCT</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> <span class="operator">&lt;</span>列名<span class="operator">&gt;</span>,<span class="operator">&lt;</span>列名<span class="operator">&gt;</span>... <span class="keyword">FROM</span> <span class="operator">&lt;</span>表名称<span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p><code>DISTINCT</code> 用于返回一列中 所有 的 唯一的值，即去重。</p>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114083113.png" alt="image-20220114083112011"></p>
<blockquote>
<p>注意，去重是按照多个列为一组数据去对比，比如DISTINCT后面跟了两个列名，那么对比时是需要两个都不相等才算不重复。</p>
<p>如下</p>
</blockquote>
<p>数据：</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425170154.png" alt="image-20220425170110968"></p>
<blockquote>
<p>SQL：SELECT DISTINCT uid,exam_id FROM exam_record</p>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425170137.png" alt="image-20220425170137789"></p>
<p>如果想要单独取一列不带重复需要用括号，</p>
<blockquote>
<p>SQL：</p>
</blockquote>
<h4 id="ORDER-BY"><a href="#ORDER-BY" class="headerlink" title="ORDER BY"></a>ORDER BY</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span>,<span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span>.. <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="operator">&lt;</span>colum_name<span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p><strong>ORDER BY 语句用于对结果集进行排序。</strong></p>
<p><code>ORDER BY</code> 语句用于根据指定的列对结果集进行排序。</p>
<p><code>ORDER BY</code> 语句默认按照升序对记录进行排序。</p>
<p>如果您希望按照降序对记录进行排序，可以使用 <code>DESC</code> 关键字。</p>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114083631.png" alt="image-20220114083630892"></p>
<h4 id="LIKE"><a href="#LIKE" class="headerlink" title="LIKE"></a>LIKE</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> field1, field2,...fieldN </span><br><span class="line"><span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line"><span class="keyword">WHERE</span> field1 <span class="keyword">LIKE</span> condition1 [<span class="keyword">AND</span> <span class="operator">/</span> <span class="keyword">OR</span>] filed2 <span class="operator">=</span> <span class="operator">&lt;</span><span class="string">&#x27;somevalue&#x27;</span><span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>你可以在 WHERE 子句中指定任何条件。</li>
<li>你可以在 WHERE 子句中使用LIKE子句。</li>
<li>你可以使用LIKE代替等号 **=**。</li>
<li><code>LIKE</code> 通常与 <strong>%</strong> 一同使用， <code>LIKE</code> 中使用百分号 **%**字符来表示任意字符，类似于UNIX/正则表达式中的 *****，更多通配符可查看 [正则表达式](# 正则表达式)。</li>
<li>你可以使用 <code>AND</code> 或者 <code>OR</code> 指定一个或多个条件。</li>
<li>你可以在 <code>DELETE</code> 或 <code>UPDATE</code> 命令中使用 <code>WHERE...LIKE</code> 子句来指定条件。</li>
</ul>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114082655.png" alt="image-20220114082654432"></p>
<h4 id="TOP"><a href="#TOP" class="headerlink" title="TOP"></a>TOP</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># top 后面跟的是数字或百分比，表示前多少个，或前百分之多少个 </span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> TOP number<span class="operator">/</span><span class="keyword">percent</span> <span class="operator">&lt;</span>colum_names<span class="operator">&gt;</span> <span class="keyword">FROM</span> table_name</span><br></pre></td></tr></table></figure>

<blockquote>
<p>但是，注意 <strong>MySQL不支持TOP</strong>语法。</p>
<p>但是<strong>MySQL</strong>的<code>limit</code>等价于<strong>TOP</strong>。</p>
</blockquote>
<h4 id="IN"><a href="#IN" class="headerlink" title="IN"></a>IN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>column_names<span class="operator">&gt;</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line"><span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="keyword">IN</span> (value1,value2,...)</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114100459.png" alt="image-20220114100458787"></p>
<h4 id="BETWEEN"><a href="#BETWEEN" class="headerlink" title="BETWEEN"></a>BETWEEN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>column_names<span class="operator">&gt;</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_names<span class="operator">&gt;</span></span><br><span class="line"><span class="keyword">BETWEEN</span> <span class="operator">&lt;</span>value1<span class="operator">&gt;</span> <span class="keyword">AND</span> <span class="operator">&lt;</span>value2<span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114101113.png" alt="image-20220114101112587"></p>
<h4 id="NULL-1"><a href="#NULL-1" class="headerlink" title="NULL"></a>NULL</h4><blockquote>
<p>在<code>MySQL</code>中使用<code>SELECT</code>以及<code>WHERE</code>来读取数据表中的数据，但是当提供的查询条件为<code>NULL</code>时，该命令可能无法正常工作。</p>
<p>关于<code>NULL</code>的条件运算符是比较特殊的，不能使用<code>=NULL</code> 或<code>！=NULL</code>在列表中查找<code>NULL</code>值。</p>
<p>在<code>MySQL</code>中，<code>NULL</code>值与其他任何值的比较（即使是<code>NULL</code>）都会返回<code>NULL</code>，即<code>NULL=NULL</code>返回<code>NULL</code>。 </p>
<p>为了解决这个问题，<code>MySQL</code>提供了三大<strong>运算符</strong>：</p>
<ul>
<li><strong>IS NULL</strong>：当列的值是<code>NULL</code>，返回<code>TRUE</code>。</li>
<li><strong>IS NOT NULL</strong>：当列的值不是<code>NULL</code>，返回<code>TRUE</code>。</li>
<li>**&lt;=&gt;**：比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 <code>NULL</code> 时返回 <code>true</code>。</li>
</ul>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 获取所有数据</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line"></span><br><span class="line"># 使用<span class="operator">=</span>和！<span class="operator">=</span>来过滤，结果显示并没有起作用</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="operator">=</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="operator">!=</span> <span class="keyword">NULL</span>;</span><br><span class="line"></span><br><span class="line"># 使用<span class="keyword">IS</span> <span class="keyword">NULL</span>和<span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> 以及 <span class="operator">&lt;=&gt;</span></span><br><span class="line"># 找等空列</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="keyword">IS</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="operator">&lt;=&gt;</span> <span class="keyword">NULL</span>;</span><br><span class="line"># 找非空列</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="operator">&lt;</span>column_name<span class="operator">&gt;</span> <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117174303.png" alt="image-20220117174303582"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117175023.png" alt="image-20220117175023190"></p>
<h3 id="DATE-1"><a href="#DATE-1" class="headerlink" title="DATE"></a>DATE</h3><p>在MySQL中存在四种不同时的时间类型，默认为DateTime，他会存入<code>YYYY-MM-DD HH:MM:SS</code>格式的时间。</p>
<blockquote>
<p>当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。</p>
<p>只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。</p>
<p>MySQL 使用下列数据类型在数据库中存储日期或日期/时间值</p>
<ul>
<li>DATE - 格式 YYYY-MM-DD</li>
<li>DATETIME - 格式: YYYY-MM-DD HH:MM:SS</li>
<li>TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS</li>
<li>YEAR - 格式 YYYY 或 YY</li>
</ul>
</blockquote>
<h4 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h4><table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_now.asp">NOW()</a></td>
<td align="left">返回当前的日期和时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_curdate.asp">CURDATE()</a></td>
<td align="left">返回当前的日期</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_curtime.asp">CURTIME()</a></td>
<td align="left">返回当前的时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_date.asp">DATE()</a></td>
<td align="left">提取日期或日期/时间表达式的日期部分</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_extract.asp">EXTRACT()</a></td>
<td align="left">返回日期/时间按的单独部分</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_date_add.asp">DATE_ADD()</a></td>
<td align="left">给日期添加指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_date_sub.asp">DATE_SUB()</a></td>
<td align="left">从日期减去指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_datediff_mysql.asp">DATEDIFF()</a></td>
<td align="left">返回两个日期之间的天数</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_date_format.asp">DATE_FORMAT()</a></td>
<td align="left">用不同的格式显示日期/时间</td>
</tr>
</tbody></table>
<p>当我们想截取<code>年月</code>/<code>月日</code>时，我们可以使用MySQL自带的时间函数：<code>date_format(时间列名,&#39;时间格式&#39;)</code>，如：<code>date_format(N_time,&#39;%m-%d&#39;)</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> N_time,date_format(N_time,<span class="string">&#x27;%m-%d&#x27;</span>) <span class="keyword">AS</span> <span class="type">time</span> <span class="keyword">from</span> notice</span><br></pre></td></tr></table></figure>

<p>结果如图：</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20210701230325.png" alt="image-20210303122059287"></p>
<h3 id="UNION"><a href="#UNION" class="headerlink" title="UNION"></a>UNION</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> expression1, expression2, ... expression_n</span><br><span class="line"><span class="keyword">FROM</span> tables</span><br><span class="line">[<span class="keyword">WHERE</span> conditions]</span><br><span class="line"><span class="keyword">UNION</span> [<span class="keyword">ALL</span> <span class="operator">|</span> <span class="keyword">DISTINCT</span>]</span><br><span class="line"><span class="keyword">SELECT</span> expression1, expression2, ... expression_n</span><br><span class="line"><span class="keyword">FROM</span> tables</span><br><span class="line">[<span class="keyword">WHERE</span> conditions];</span><br></pre></td></tr></table></figure>

<blockquote>
<p><strong>请注意</strong>，<code>UNION</code> 内部的 <code>SELECT</code> 语句必须拥有<strong>相同数量的列</strong>。列也必须拥有<strong>相似的数据类型</strong>。同时，每条 <code>SELECT</code> 语句中的<strong>列的顺序</strong>必须相同。</p>
<p><code>MySQL UNION</code> 操作符用于连接<strong>两个</strong>以上的 <code>SQL</code>语句的结果组合到一个结果集合中。</p>
<p>多个 <code>SELECT</code> 语句会删除重复的数据。</p>
<p>默认地，<code>UNION</code> 操作符选取不同的值。如果允许重复的值，请使用 <code>UNION ALL</code>。</p>
<p>另外，<code>UNION</code> 结果集中的列名总是等于 <code>UNION</code> 中第一个 <code>SELECT</code> 语句中的列名。</p>
</blockquote>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114091027.png" alt="image-20220114091025882"></p>
<p>union后的结果</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114091052.png" alt="image-20220114091051553"></p>
<h3 id="JOIN"><a href="#JOIN" class="headerlink" title="JOIN"></a>JOIN</h3><blockquote>
<p><strong>join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。</strong></p>
</blockquote>
<p>首先,我们看准备的数据</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114113256.png" alt="image-20220114113255206"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 查看有学分的学生的学号,姓名,学分</span><br><span class="line"><span class="keyword">SELECT</span> stu.stu_num,stu.stu_name,stus.stu_score </span><br><span class="line"><span class="keyword">from</span> table_student <span class="keyword">as</span> stu,table_score <span class="keyword">as</span> stus</span><br><span class="line"><span class="keyword">where</span> stu.id <span class="operator">=</span> stus.stu_id;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114105626.png" alt="image-20220114105625221"></p>
<p>下面我们使用<code>join</code>来完成上面的操作.</p>
<h4 id="INNER-JOIN"><a href="#INNER-JOIN" class="headerlink" title="INNER JOIN"></a>INNER JOIN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 筛选两表都符合条件的数据</span><br><span class="line"><span class="keyword">SELECT</span> stu.stu_num,stu.stu_name,stus.std_score </span><br><span class="line"><span class="keyword">FROM</span> table_student <span class="keyword">as</span> stu </span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> table_score <span class="keyword">as</span> stus </span><br><span class="line"><span class="keyword">ON</span> stu.id <span class="operator">=</span> stus.stu_id</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114105956.png" alt="image-20220114105955687"></p>
<h4 id="LEFT-JOIN"><a href="#LEFT-JOIN" class="headerlink" title="LEFT JOIN"></a>LEFT JOIN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 返回<span class="keyword">LEFT</span> <span class="keyword">JOIN</span> 关键字左面表的所有行,并将右表符合条件的数据放在合适的位置</span><br><span class="line"><span class="keyword">SELECT</span> stu.stu_num,stu.stu_name,stus.std_score </span><br><span class="line"><span class="keyword">FROM</span> table_student <span class="keyword">as</span> stu </span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> table_score <span class="keyword">as</span> stus </span><br><span class="line"><span class="keyword">ON</span> stu.id <span class="operator">=</span> stus.stu_id</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114112313.png" alt="image-20220114112312585"></p>
<h4 id="RIGHT-JOIN"><a href="#RIGHT-JOIN" class="headerlink" title="RIGHT JOIN"></a>RIGHT JOIN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 返回<span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> 关键字右面表的所有行,并将左表符合条件的数据放在合适的位置</span><br><span class="line"><span class="keyword">SELECT</span> stu.stu_num,stu.stu_name,stus.std_score </span><br><span class="line"><span class="keyword">FROM</span> table_student <span class="keyword">as</span> stu </span><br><span class="line"><span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> table_score <span class="keyword">as</span> stus </span><br><span class="line"><span class="keyword">ON</span> stu.id <span class="operator">=</span> stus.stu_id</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114113023.png" alt="image-20220114113022339"></p>
<h4 id="FULL-JOIN"><a href="#FULL-JOIN" class="headerlink" title="FULL JOIN"></a>FULL JOIN</h4><blockquote>
<p> <code>FULL JOIN</code> 称为 <code>FULL OUTER JOIN</code>。但是<code>MySQL</code>不支持.</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 只要其中某个表存在匹配，<span class="keyword">FULL</span> <span class="keyword">JOIN</span> 关键字就会返回行</span><br><span class="line"><span class="keyword">SELECT</span> stu.stu_num,stu.stu_name,stus.std_score </span><br><span class="line"><span class="keyword">FROM</span> table_student <span class="keyword">as</span> stu </span><br><span class="line"><span class="keyword">FULL</span> <span class="keyword">JOIN</span> table_score <span class="keyword">as</span> stus </span><br><span class="line"><span class="keyword">ON</span> stu.id <span class="operator">=</span> stus.stu_id</span><br></pre></td></tr></table></figure>

<p>通过上面的实验你可以知道,</p>
<blockquote>
<ul>
<li>JOIN: 如果表中有至少一个匹配，则返回行</li>
<li>LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行</li>
<li>RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行</li>
<li>FULL JOIN: 只要其中一个表中存在匹配，就返回行</li>
</ul>
</blockquote>
<h3 id="VIEW"><a href="#VIEW" class="headerlink" title="VIEW"></a>VIEW</h3><p>视图是基于 <code>SQL</code> 语句的结果集的可视化的表。</p>
<p>视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 <code>SQL</code> 函数、<code>WHERE</code> 以及 <code>JOIN</code> 语句，我们也可以提交数据，就像这些来自于某个单一的表。</p>
<p><strong>注释：</strong>数据库的设计和结构不会受到视图中的函数、<code>where</code> 或 <code>join</code> 语句的影响。</p>
<h4 id="创建视图"><a href="#创建视图" class="headerlink" title="创建视图"></a>创建视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> <span class="operator">&lt;</span>view_name<span class="operator">&gt;</span> <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>column_names<span class="operator">&gt;</span></span><br><span class="line"><span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line"><span class="keyword">WHERE</span> <span class="operator">&lt;</span><span class="keyword">condition</span><span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>



<h3 id="DESC"><a href="#DESC" class="headerlink" title="DESC"></a>DESC</h3><blockquote>
<p>对数据进行降序排序，与<code>sort by</code>一同使用</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"># Employee 表：</span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"># <span class="operator">|</span> id <span class="operator">|</span> salary <span class="operator">|</span></span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">1</span>  <span class="operator">|</span> <span class="number">100</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">2</span>  <span class="operator">|</span> <span class="number">200</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">3</span>  <span class="operator">|</span> <span class="number">300</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"><span class="keyword">SELECT</span> salary </span><br><span class="line"><span class="keyword">from</span> Employee</span><br><span class="line">sort <span class="keyword">by</span> salary <span class="keyword">DESC</span></span><br></pre></td></tr></table></figure>



<h3 id="DESTINCT"><a href="#DESTINCT" class="headerlink" title="DESTINCT"></a>DESTINCT</h3><blockquote>
<p>对数据进行去重处理，用在列明之前</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"># Employee 表：</span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"># <span class="operator">|</span> id <span class="operator">|</span> salary <span class="operator">|</span></span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">1</span>  <span class="operator">|</span> <span class="number">100</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">2</span>  <span class="operator">|</span> <span class="number">200</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">|</span> <span class="number">3</span>  <span class="operator">|</span> <span class="number">200</span>    <span class="operator">|</span></span><br><span class="line"># <span class="operator">+</span><span class="comment">----+--------+</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> salary </span><br><span class="line"><span class="keyword">from</span> Employee</span><br></pre></td></tr></table></figure>



<h3 id="临时表"><a href="#临时表" class="headerlink" title="临时表"></a>临时表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> TEMPORARY <span class="keyword">TABLE</span> SalesSummary (</span><br><span class="line">     product_name <span class="type">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">     total_sales <span class="type">DECIMAL</span>(<span class="number">12</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0.00</span>, </span><br><span class="line">     avg_unit_price <span class="type">DECIMAL</span>(<span class="number">7</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0.00</span>, </span><br><span class="line">     total_units_sold <span class="type">INT</span> UNSIGNED <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0</span>);</span><br></pre></td></tr></table></figure>

<h2 id="函数-1"><a href="#函数-1" class="headerlink" title="函数"></a>函数</h2><blockquote>
<p>MySQL自带很多函数供我们使用，为我们的查询提供了极大的方便。</p>
</blockquote>
<h3 id="Math函数"><a href="#Math函数" class="headerlink" title="Math函数"></a>Math函数</h3><h4 id="MAX"><a href="#MAX" class="headerlink" title="MAX"></a>MAX</h4><p>求一列的最大值</p>
<h4 id="MIN"><a href="#MIN" class="headerlink" title="MIN"></a>MIN</h4><p>求一列的最小值</p>
<h4 id="SUM"><a href="#SUM" class="headerlink" title="SUM"></a>SUM</h4><p>求一列的和</p>
<h4 id="ROUND"><a href="#ROUND" class="headerlink" title="ROUND"></a>ROUND</h4><p>返回离值X最近的数（四舍五入），支持小数位位数</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 四舍五入</span><br><span class="line">SELECT ROUND(1.4) = 1</span><br><span class="line"></span><br><span class="line"># 两位小数</span><br><span class="line">SELECT ROUND(1.455，2) = 1.46</span><br></pre></td></tr></table></figure>

<h4 id="RAND"><a href="#RAND" class="headerlink" title="RAND"></a>RAND</h4><p>随机数，范围[0,1)</p>
<h3 id="字符函数"><a href="#字符函数" class="headerlink" title="字符函数"></a>字符函数</h3><h4 id="SUBSTR"><a href="#SUBSTR" class="headerlink" title="SUBSTR"></a>SUBSTR</h4><p><code>SUBSTR</code>是一个字符函数，类似于Java中的<code>substring()</code>方法，但略有不同。</p>
<p>语法，注意在MySQL中<code>SUBSTR</code>是从1开始的，不是0。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 使用方法1,代表从start开始，截取length长度的字符串</span><br><span class="line">SUBSTR(s, start, length)</span><br><span class="line"></span><br><span class="line"># 使用方法2，代表从start开始，截取length长度的字符串</span><br><span class="line">SUBSTR(s FROM start FOR length)</span><br></pre></td></tr></table></figure>

<h4 id="SUBSTRING"><a href="#SUBSTRING" class="headerlink" title="SUBSTRING"></a>SUBSTRING</h4><p>等同于 [SUBSTR]( # SUBSTR)。</p>
<h4 id="CONCAT"><a href="#CONCAT" class="headerlink" title="CONCAT"></a>CONCAT</h4><p>有字符串截取，自然也是有字符串拼接的，在MySQL中的字符串拼接就是<code>CONCAT(str1,str2...strN)</code>。</p>
<p><code>CONCAT()</code>函数用于合并多个字符串。</p>
<h4 id="CONCAT-WS"><a href="#CONCAT-WS" class="headerlink" title="CONCAT_WS"></a>CONCAT_WS</h4><p>在拼接字符串时，有时候我们需要添加分隔符。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CONCAT_WS(&#x27;,&#x27;,&#x27;hi&#x27;,&#x27;how are you&#x27;)</span><br></pre></td></tr></table></figure>

<h4 id="LEFT"><a href="#LEFT" class="headerlink" title="LEFT"></a>LEFT</h4><p><code>LEFT</code>是一个字符函数，如<code>LEFT(&quot;mysql&quot;, 2)</code>表示取字符的前三个字符，即<code>my</code>。</p>
<p><strong>语法</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 从字符串中提取n个字符</span><br><span class="line">LEFT(string, number)</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意，<code>number</code>是可以大于字符长度的，这样取出来的就是整个字符，且字符长度为字符串的长度。</p>
<p>举个:chestnut:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select LENGTH(LEFT(&#x27;mysql&#x27;,10))</span><br></pre></td></tr></table></figure>

<p>上面SQL的结果就是 5 ，而不是10。</p>
</blockquote>
<h4 id="RIGHT"><a href="#RIGHT" class="headerlink" title="RIGHT"></a>RIGHT</h4><p>参照 [LEFT](# LEFT)。</p>
<h4 id="LCASE"><a href="#LCASE" class="headerlink" title="LCASE"></a>LCASE</h4><h4 id="LOWER"><a href="#LOWER" class="headerlink" title="LOWER"></a>LOWER</h4><h4 id="UPCASE"><a href="#UPCASE" class="headerlink" title="UPCASE"></a>UPCASE</h4><h3 id="时间函数"><a href="#时间函数" class="headerlink" title="时间函数"></a>时间函数</h3><h4 id="DATE-2"><a href="#DATE-2" class="headerlink" title="DATE"></a>DATE</h4><p><code>DATE</code>函数返回一个datetime类型的时间的<strong>年月日</strong>。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DATE(&#x27;2021-01-01 12:20:00&#x27;) = &#x27;2021-01-01&#x27;</span><br></pre></td></tr></table></figure>

<h4 id="DATE-FORMAT"><a href="#DATE-FORMAT" class="headerlink" title="DATE_FORMAT"></a>DATE_FORMAT</h4><p><code>DATE_FORMAT(d,f)</code>日期格式化，两个参数，一个日期，一个format，将日期按照format格式化</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DATE_FORMAT(&#x27;2021-01-01&#x27;,&#x27;%Y - %m - %d&#x27;)</span><br></pre></td></tr></table></figure>



<h4 id="DATEDIFF"><a href="#DATEDIFF" class="headerlink" title="DATEDIFF"></a>DATEDIFF</h4><p><code>datediff(d1,d2)</code>，返回两时间之间的天数差（d1 - d2）</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DATEDIFF(&#x27;2021-01-05 12:20:00&#x27;,&#x27;2021-01-01 12:20:00&#x27;) = 4</span><br></pre></td></tr></table></figure>

<h4 id="TIMEDIFF"><a href="#TIMEDIFF" class="headerlink" title="TIMEDIFF"></a>TIMEDIFF</h4><p><code>TIMEDIFF(t1,t2)</code>，返回两时间差，</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">```</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">#### TIMESTAMPDIFF</span><br><span class="line"></span><br><span class="line">`TIMESTAMPDIFF(unit,d1,d2)`，计算时间差(d2-d1)，按照unit为单位计算</span><br><span class="line"></span><br><span class="line">```mysql</span><br><span class="line"># unit = DAY ,以天为单位返回</span><br><span class="line">timestampdiff(DAY,&#x27;2003-02-01&#x27;,&#x27;2003-05-01&#x27;)</span><br><span class="line"></span><br><span class="line"># unit = MONTH，以月为单位返回，</span><br><span class="line">TIMESTAMPDIFF(MONTH,&#x27;2003-02-01&#x27;,&#x27;2003-05-01&#x27;)</span><br><span class="line"></span><br><span class="line"># unit 还可以等于YEAR、MINUTE</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220425100035.png" alt="image-20220425095957458"></p>
<h3 id="高级函数"><a href="#高级函数" class="headerlink" title="高级函数"></a>高级函数</h3><h4 id="IF"><a href="#IF" class="headerlink" title="IF"></a>IF</h4><p><code>IF</code>是一个三元函数，类似于Java的三元表达式，他会计算第一个表达式的结果，如果为真就返回第二个表达式，否则返回第三个。</p>
<p><strong>语法</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 计算express，为真返回v1，否则返回v2</span><br><span class="line">IF(express,v1,v2)</span><br></pre></td></tr></table></figure>



<h4 id="CASE"><a href="#CASE" class="headerlink" title="CASE"></a>CASE</h4><p><code>CASE</code>是MySQL的高级函数之一，其实用类似于Java的case。</p>
<p><strong>语法</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"># 用法1，简单，机械有一定的限制</span><br><span class="line">CASE expression</span><br><span class="line">    WHEN condition1 THEN result1</span><br><span class="line">    WHEN condition2 THEN result2</span><br><span class="line">    ...</span><br><span class="line">    WHEN conditionN THEN resultN</span><br><span class="line">    ELSE result</span><br><span class="line">END</span><br><span class="line"></span><br><span class="line"># 用法2，简单，灵活</span><br><span class="line">CASE </span><br><span class="line">    WHEN &lt;expression1&gt; THEN result1</span><br><span class="line">    WHEN &lt;expression2&gt; THEN result2</span><br><span class="line">    ...</span><br><span class="line">    WHEN &lt;expressionN&gt; THEN resultN</span><br><span class="line">    ELSE result</span><br><span class="line">END</span><br></pre></td></tr></table></figure>

<p>对于两种使用来说，<code>CASE</code>表示函数的开始，<code>END</code>表示函数的结束。</p>
<p><strong>用法1</strong></p>
<p>针对一个表达式的不同值进行匹配，如果<code>condition1</code>成立则返回<code>result1</code>，其余情况同理，当没有匹配项，则返回result。</p>
<p><strong>用法2</strong></p>
<p>针对多个表达式进行匹配，类似于<code>if-else</code>，如果<code>expression1</code>成立则返回<code>result1</code>，其余情况同理，当没有匹配项，则返回result。</p>
<p><strong>注意</strong></p>
<p><code>THEN</code>和<code>ELSE</code></p>
<h4 id="DENSE-RANK"><a href="#DENSE-RANK" class="headerlink" title="DENSE_RANK"></a>DENSE_RANK</h4><p><code>DENSE_RANK</code>是一个窗口函数，他为分区或结果集中的每一行数据分配排名，且排名没有间隙，如果有多个相同排名的值，那么他们的排名值是相同的。</p>
<p><strong>语法</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">DENSE_RANK() OVER (</span><br><span class="line">    PARTITION BY &lt;expression&gt;[&#123;,&lt;expression&gt;...&#125;]</span><br><span class="line">    ORDER BY &lt;expression&gt; [ASC|DESC], [&#123;,&lt;expression&gt;...&#125;]</span><br><span class="line">) </span><br></pre></td></tr></table></figure>

<ul>
<li><code>PARTITION BY</code>，将<code>FROM</code>字句生成的结果按条件分区，该函数应用于每个分区。</li>
<li><code>ORDER BY</code>，指定每个分区的排序规则。</li>
</ul>
<p><strong>实例</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"># 表Scores</span><br><span class="line">+----+-------+</span><br><span class="line">| id | score |</span><br><span class="line">+----+-------+</span><br><span class="line">| 1  | 3.50  |</span><br><span class="line">| 2  | 3.65  |</span><br><span class="line">| 3  | 4.00  |</span><br><span class="line">| 4  | 3.85  |</span><br><span class="line">| 5  | 4.00  |</span><br><span class="line">| 6  | 3.65  |</span><br><span class="line">+----+-------+</span><br><span class="line"></span><br><span class="line"># SQL</span><br><span class="line">SELECT score,DENSE_RANK() OVER (ORDER BY score DESC) &#x27;rank&#x27;</span><br><span class="line">FROM Scores</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220412111904.png" alt="image-20220412111903967"></p>
<h1 id="MySQL高级"><a href="#MySQL高级" class="headerlink" title="MySQL高级"></a>MySQL高级</h1><h2 id="自建函数"><a href="#自建函数" class="headerlink" title="自建函数"></a>自建函数</h2><blockquote>
<p>MySQL自带很多函数，这些函数在我们查询时提供了很大的帮助，但是由于我们业务场景的不同，MySQL不可能会兼顾到所有的业务场景，所以它提供了自建函数（CREATE FUNCTION）给我们使用。</p>
<p>参考：<a target="_blank" rel="noopener" href="https://blog.csdn.net/weixin_45866849/article/details/119719547">https://blog.csdn.net/weixin_45866849/article/details/119719547</a></p>
</blockquote>
<h3 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> function_name ( [function_parameter] ) <span class="operator">/</span><span class="operator">/</span>括号是必须的，参数是可选的</span><br><span class="line"><span class="keyword">RETURNS</span> type</span><br><span class="line">[ characteristic ...] routine_body</span><br></pre></td></tr></table></figure>

<ul>
<li>function_name：自建函数名称</li>
<li>function_parameter：自建函数的参数，可选，还可以设置是输入参数还是输出参数，用 IN 、OUT、INOUT表示，IN：表示输入参数，OUT：表示输出参数，INOUT：表示既可以输入也可以输出。</li>
<li>type：返回值类型</li>
<li>characteristic：指定该函数的特性，</li>
<li>routine_body：函数体</li>
</ul>
<p>characteristic的取值为</p>
<table>
<thead>
<tr>
<th>值</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>language sql</td>
<td>说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL</td>
</tr>
<tr>
<td>[not] deterministic</td>
<td>指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。</td>
</tr>
<tr>
<td>{contains sql \no sql \ reads sql data \modifies sql data}</td>
<td>指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA：说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。</td>
</tr>
<tr>
<td>sql_security{definer\invoker}</td>
<td>指明谁有权限来执行。DEFINER 表示只有定义者才能执行；INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。</td>
</tr>
<tr>
<td>comment ‘string’</td>
<td>注释信息，可以用来描述存储过程或函数</td>
</tr>
</tbody></table>
<p>表链接源：<a target="_blank" rel="noopener" href="https://juejin.cn/post/6844903461234933773">https://juejin.cn/post/6844903461234933773</a></p>
<p>其实，这个我也不太懂，也没咋用到。</p>
<h3 id="变量"><a href="#变量" class="headerlink" title="变量"></a>变量</h3><p>在MySQL的函数中，变量的定义是在begin之前定义的，变量的范围旨在该函数中生效。</p>
<p>举个:chestnut:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line"># 表Employee</span><br><span class="line">+-------------+------+</span><br><span class="line">| Column Name | Type |</span><br><span class="line">+-------------+------+</span><br><span class="line">| id          | int  |</span><br><span class="line">| salary      | int  |</span><br><span class="line">+-------------+------+</span><br><span class="line"># 函数1</span><br><span class="line">DELIMETER// # 声明end的结束符为//，不与MySQL语句的结束符冲突</span><br><span class="line">create function getSalary(N INT) returns INT</span><br><span class="line">BEGIN</span><br><span class="line">	DECLARE rt_salary INT; # 声明薪水变量</span><br><span class="line">	SET N:=N-1; // 设置n=n-1</span><br><span class="line">	RETURN(</span><br><span class="line">    	select </span><br><span class="line">        	salary</span><br><span class="line">        INTO </span><br><span class="line">        	rt_salary</span><br><span class="line">        from</span><br><span class="line">        	Employee</span><br><span class="line">    );</span><br><span class="line">END//</span><br><span class="line"></span><br><span class="line"># 函数2</span><br><span class="line">DELIMETER// # 声明end的结束符为//，不与MySQL语句的结束符冲突</span><br><span class="line">create function getSalary(N INT) returns INT</span><br><span class="line">BEGIN</span><br><span class="line">	DECLARE rt_salary INT; # 声明薪水变量</span><br><span class="line">	</span><br><span class="line">    select </span><br><span class="line">       	salary</span><br><span class="line">    INTO </span><br><span class="line">       	rt_salary</span><br><span class="line">    from</span><br><span class="line">        Employee;</span><br><span class="line">        </span><br><span class="line">    RETURN rt_salary;</span><br><span class="line">END//</span><br></pre></td></tr></table></figure>



<h2 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h2><blockquote>
<p>索引是一个（？）的（？）。</p>
<p>索引有利有弊。在会用的人手里，利大大大于弊。在不会用的人手里，利弊不相上下（目前的我就是这个级别，所以说起索引，我也是蒙的很）。</p>
<p>首先啊，经过两天的基础学习，我知道了要想知道索引的好坏，那么你必须要先了解什么是索引，然后是它的数据结构—B+树。其次，你需要知道它是如何存储的，存储在哪，怎么调用。</p>
</blockquote>
<p><strong>准备</strong></p>
<ul>
<li>B 树</li>
<li>B+ 树</li>
<li>B+ 树</li>
<li>B+ 树</li>
<li>。。。</li>
</ul>
<p><strong>WHAT？</strong></p>
<p>在关系数据库中，索引是一种<strong>单独</strong>的、<strong>物理</strong>的，对数据库表中的<strong>一列或多列</strong>的值进行<strong>排序</strong>的一种<strong>存储结构</strong>（B+树），他是表中一列或多列值的集合和一系列指向实际数据的指针集合（类似字典K-V）。</p>
<p>所以，索引独立于数据表，也是一个文件，所以索引也是<strong>存储在磁盘</strong>上的，每次开启MySQL客户端后，会加载索引文件到<strong>内存</strong>中。</p>
<p><strong>WHY？</strong></p>
<p>为什么使用索引？</p>
<p>因为索引能提高我们查询的效率，为什么能够提高我们的查询效率？这就需要说一下数据库查询的过程。</p>
<p>当数据库中有大量数据时，想要查询一条数据，最简单粗暴的方式就是逐一匹配（全表搜索），这既是最慢的，慢在两点，其一，<strong>待匹配的数据量</strong>大且<strong>无序</strong>，其二，加载数据所需的<strong>IO</strong>次数多，也会造成时间浪费。</p>
<p>为了减少上面所需时间，我们就需要从这两方面下手。</p>
<p>所以出现了以<strong>B+树</strong>为数据结构的索引。</p>
<p>B+树能保证IO次数不会太多，索引是行数据的<strong>关键值</strong>得集合，保证了小体量的数据，一个索引文件能够存储大量的索引。</p>
<p>创建索引时，你需要确保该索引是应用在 <code>SQL</code> 查询语句的条件(一般作为 <code>WHERE</code> 子句的条件)，即保证索引能起作用。</p>
<p>实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。</p>
<p><strong>优点</strong></p>
<p><code>MySQL</code>索引的建立对于<code>MySQL</code>的高效运行是很重要的，索引可以大大提高<code>MySQL</code>的检索速度。</p>
<p>打个比方，如果合理的设计且使用索引的<code>MySQL</code>是一辆兰博基尼的话，那么没有设计和使用索引的<code>MySQL</code>就是一个人力三轮车。</p>
<p>拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。</p>
<p><strong>缺点</strong></p>
<p>虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行<code>INSERT</code>、<code>UPDATE</code>和<code>DELETE</code>。因为更新表时，<code>MySQL</code>不仅要保存数据，还要保存一下索引文件。</p>
<p>索引文件会占用磁盘空间，所以，索引不是越多越好。</p>
<p><strong>分类</strong></p>
<p>根据底层实现可以分为<strong>B-Tree索引</strong>和<strong>哈希索引</strong>。</p>
<p>根据存储方式可以分<strong>聚簇索引</strong>和<strong>非聚簇索引</strong>，下还可分为<strong>单列索引</strong>、<strong>组合索引</strong>、<strong>普通索引</strong>、<strong>唯一索引</strong>、<strong>覆盖索引</strong></p>
<hr>
<h3 id="聚簇索引"><a href="#聚簇索引" class="headerlink" title="聚簇索引"></a>聚簇索引</h3><blockquote>
<p>聚簇索引，按我的意思来说就是，其指向的地址包含行数据的全数据，即找到该索引，你就能通过索引指向的地址找到你需要的所有数据。有且仅有一个聚簇索引，可以等价理解为主键，其实他就是主键（有主键的情况下）。</p>
<p>官方原话：</p>
<p>The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.</p>
<p>大致意思：</p>
<p>聚簇索引是InnoDB引擎中主键索引的术语。为了加快主键列的查询和排序，InnoDB的聚簇索引存储的是主键索引列。所以选择主键时需要认真选，不然后期维护很费时间和钱。</p>
<p>当然也是会有意外情况的，比如我们建表时没有选择主键，那么MySQL会如何处理呢？</p>
<p>① 聚簇索引默认选择主键。</p>
<p>② 如果表中没有定义主键，InnoDB会选择一个唯一（unique index）的非空索引代替。</p>
<p>③ 如果没有上面那样的索引，InnoDB会自动地创建一个6字节的隐式主键作为聚簇索引。</p>
</blockquote>
<p>举个:chestnut:，来说明聚簇索引具体的功能。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">#数据表有以下字段</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> db_student(</span><br><span class="line">    id <span class="type">int</span>(<span class="number">8</span>) unsigned auto_increment <span class="keyword">primary</span> key,</span><br><span class="line">    stu_num  <span class="type">varchar</span>(<span class="number">8</span>)  <span class="keyword">null</span>,</span><br><span class="line">    stu_name <span class="type">varchar</span>(<span class="number">16</span>) <span class="keyword">null</span>,</span><br><span class="line">    stu_age  <span class="type">int</span>(<span class="number">3</span>)      <span class="keyword">null</span>,</span><br><span class="line">    stu_sex  <span class="type">varchar</span>(<span class="number">1</span>)  <span class="keyword">null</span>,</span><br><span class="line">    <span class="keyword">constraint</span> stu_num</span><br><span class="line">    <span class="keyword">unique</span> (stu_num)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_student(stu_num,stu_name,stu_age,stu_sex) <span class="keyword">values</span>(<span class="string">&#x27;2017&#x27;</span>,<span class="string">&#x27;张三&#x27;</span>,<span class="number">20</span>,<span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_student(stu_num,stu_name,stu_age,stu_sex) <span class="keyword">values</span>(<span class="string">&#x27;2018&#x27;</span>,<span class="string">&#x27;里斯&#x27;</span>,<span class="number">20</span>,<span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_student(stu_num,stu_name,stu_age,stu_sex) <span class="keyword">values</span>(<span class="string">&#x27;2019&#x27;</span>,<span class="string">&#x27;李四&#x27;</span>,<span class="number">20</span>,<span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_student(stu_num,stu_name,stu_age,stu_sex) <span class="keyword">values</span>(<span class="string">&#x27;2020&#x27;</span>,<span class="string">&#x27;王五&#x27;</span>,<span class="number">20</span>,<span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"></span><br><span class="line"># 主键自增，所以我们不用插入，在数据库中会自动从<span class="number">1</span>开始自增</span><br></pre></td></tr></table></figure>

<p>可以看到主键是id，那么聚簇索引就是id，你在主键的索引文件中找到对应的列，假设索引文件如下（实际不是，只是为了好体现出聚簇索引）：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>地址</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>0001</td>
</tr>
<tr>
<td>2</td>
<td>0002</td>
</tr>
<tr>
<td>3</td>
<td>0003</td>
</tr>
<tr>
<td>4</td>
<td>0004</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 此时我们查询id<span class="operator">=</span><span class="number">1</span>的数据</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"># 语句命中索引，所以我们会去索引文件查询id<span class="operator">=</span><span class="number">1</span>的数据行，找到后，根据地址去存储实际数据的文件找到该地址存储的数据，里面会包含（<span class="string">&#x27;2017&#x27;</span>,<span class="string">&#x27;张三&#x27;</span>,<span class="number">20</span>,<span class="string">&#x27;0&#x27;</span>）</span><br></pre></td></tr></table></figure>

<h3 id="非聚簇索引"><a href="#非聚簇索引" class="headerlink" title="非聚簇索引"></a>非聚簇索引</h3><blockquote>
<p>非聚簇索引也叫二级索引、辅助索引、普通索引。</p>
<p>它的叶子节点指向的是主键索引的地址。命中非聚簇索引后，通过非聚簇索引文件找到聚簇索引文件的地址，再通过聚簇索引文件找到对应的数据，<u>非聚簇索引文件 –&gt; 聚簇索引文件</u> 的这一过程也叫<strong>回表</strong>。</p>
</blockquote>
<table>
<thead>
<tr>
<th></th>
<th>聚簇索引</th>
<th>非聚簇索引</th>
</tr>
</thead>
<tbody><tr>
<td>key</td>
<td>索引列的值（不能为空）</td>
<td>索引列的值（可以为空）</td>
</tr>
<tr>
<td>value</td>
<td>指向数据存储地址</td>
<td>指像聚簇索引文件地址</td>
</tr>
<tr>
<td>叶子节点</td>
<td>存储对应的行数据的地址</td>
<td>存储对应的主键值</td>
</tr>
</tbody></table>
<p>举个:chestnut:</p>
<p>下表中，数字列为主键（聚簇索引）。</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220325105633.png" alt="image-20220325105632751"></p>
<p>图源：<a target="_blank" rel="noopener" href="https://www.jianshu.com/p/fa8192853184">https://www.jianshu.com/p/fa8192853184</a></p>
<h3 id="单列索引"><a href="#单列索引" class="headerlink" title="单列索引"></a>单列索引</h3><blockquote>
<p>一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。</p>
</blockquote>
<h3 id="组合索引"><a href="#组合索引" class="headerlink" title="组合索引"></a>组合索引</h3><blockquote>
<p>又称联合索引，说的是一个索引包含多个列。</p>
<p>创建组合索引后，索引文件中的叶子节点会同时包含每个索引列的值，并根据多列排序</p>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"># 建表时建立主键</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> `db_stu`(</span><br><span class="line">    `id` <span class="type">int</span>(<span class="number">8</span>) UNSIGNED AUTO_INCREMENT <span class="keyword">PRIMARY</span> KEY,</span><br><span class="line">    `stu_num` <span class="type">int</span>(<span class="number">8</span>),</span><br><span class="line">    `stu_name` <span class="type">varchar</span>(<span class="number">16</span>),</span><br><span class="line">    `stu_age` <span class="type">int</span>(<span class="number">3</span>)</span><br><span class="line">)ENGINE<span class="operator">=</span>InnoDB;</span><br><span class="line"></span><br><span class="line"># 建立组合索引,注意，这里不要加id，因为已经存在id索引文件了，</span><br><span class="line"># 组合索引:id,stu_num,stu_name在使用上并不会比组合索引：stu_num,stu_name <span class="operator">+</span> 主键索引：id好</span><br><span class="line"><span class="keyword">CREATE</span> INDEX index_union <span class="keyword">ON</span> db_stu (stu_num,stu_name,stu_age);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_stu(stu_num,stu_name,stu_age) <span class="keyword">values</span>(<span class="string">&#x27;2017&#x27;</span>,<span class="string">&#x27;张三&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_stu(stu_num,stu_name,stu_age) <span class="keyword">values</span>(<span class="string">&#x27;2018&#x27;</span>,<span class="string">&#x27;ls&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_stu(stu_num,stu_name,stu_age) <span class="keyword">values</span>(<span class="string">&#x27;2019&#x27;</span>,<span class="string">&#x27;里斯&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_stu(stu_num,stu_name,stu_age) <span class="keyword">values</span>(<span class="string">&#x27;2020&#x27;</span>,<span class="string">&#x27;李四&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"># 查看索引</span><br><span class="line"><span class="keyword">SHOW</span> INDEX <span class="keyword">FROM</span> db_stu;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220120154648.png" alt="image-20220120154646565"></p>
<h4 id="最左匹配"><a href="#最左匹配" class="headerlink" title="最左匹配"></a>最左匹配</h4><blockquote>
<p>说到组合索引，那就不得不说一下最左匹配原则了。</p>
<p>什么是最左匹配原则？</p>
<p>在创建了组合索引之后，并不是<code>where</code>子句中包含组合索引就能命中组合索引的，想要命中组合索引时需要一定条件的。</p>
<p>有兴趣的小伙伴可以参考官方文档：<a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html">组合索引</a>。</p>
<p>官方原话：</p>
<p>If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on <code>(col1, col2, col3)</code>, you have indexed search capabilities on <code>(col1)</code>, <code>(col1, col2)</code>, and <code>(col1, col2, col3)</code>.</p>
<p>最左匹配类似于选择省市县时，需要先选择省，缩小范围（确定下一级范围）这种意思。</p>
<p>表示假设对表中的（A，B，C）建立组合索引，那么能命中索引的场景如下</p>
<ul>
<li>（A，B，C）（A，B）（A），</li>
<li>使用通配符时，</li>
</ul>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># 联合索引为（stu_num,stu_name,stu_age）,能命中索引的情况如下	</span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_stu <span class="keyword">where</span> stu_num<span class="operator">=</span><span class="string">&#x27;2017&#x27;</span> <span class="keyword">and</span> stu_name<span class="operator">=</span><span class="string">&#x27;ww&#x27;</span> <span class="keyword">and</span> stu_age<span class="operator">=</span><span class="number">20</span>;</span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_stu <span class="keyword">where</span> stu_num<span class="operator">=</span><span class="string">&#x27;2017&#x27;</span> <span class="keyword">and</span> stu_name<span class="operator">=</span><span class="string">&#x27;ww&#x27;</span>;</span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_stu <span class="keyword">where</span> stu_num<span class="operator">=</span><span class="string">&#x27;2017&#x27;</span>;</span><br><span class="line"></span><br><span class="line"># 剩下的都不能命中索引</span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_stu <span class="keyword">where</span> stu_name <span class="operator">=</span> <span class="string">&#x27;ww&#x27;</span> <span class="keyword">and</span> stu_age<span class="operator">=</span><span class="number">20</span>;</span><br></pre></td></tr></table></figure>



<h4 id="索引下推"><a href="#索引下推" class="headerlink" title="索引下推"></a>索引下推</h4><h3 id="唯一索引"><a href="#唯一索引" class="headerlink" title="唯一索引"></a>唯一索引</h3><blockquote>
<p>它与普通索引类似，不同的就是</p>
<ul>
<li><p>索引列的值必须唯一，允许有空值。</p>
</li>
<li><p>如果是组合索引，则列值的组合必须唯一。</p>
</li>
</ul>
</blockquote>
<h3 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a>覆盖索引</h3><blockquote>
<p>由于非覆盖索引的叶子节点没有完整的数据信息，所以我们需要<strong>回表</strong>，来保证我们能取到自己所需的数据，但是回表相当于又进行一次查询，可能会涉及多次IO。</p>
<p>覆盖索引并不是一种真的索引，他只是一种针对<strong>回表</strong>的优化方案。</p>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># 创建一个表，查询其中的两个值</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> db_test(</span><br><span class="line">	`id` <span class="type">int</span> auto_increment <span class="keyword">primary</span> key,</span><br><span class="line">    `name` <span class="type">varchar</span>(<span class="number">16</span>),</span><br><span class="line">    `age` <span class="type">int</span>(<span class="number">3</span>)</span><br><span class="line">)engine<span class="operator">=</span>innodb;</span><br><span class="line"></span><br><span class="line"># 创建name为普通索引</span><br><span class="line"><span class="keyword">create</span> index index_name <span class="keyword">on</span> db_test(name);</span><br><span class="line"></span><br><span class="line"># 插入示例数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_test(name,age) <span class="keyword">values</span>(<span class="string">&#x27;zs&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_test(name,age) <span class="keyword">values</span>(<span class="string">&#x27;ls&#x27;</span>,<span class="number">20</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> db_test(name,age) <span class="keyword">values</span>(<span class="string">&#x27;ww&#x27;</span>,<span class="number">20</span>);</span><br></pre></td></tr></table></figure>

<p>业务场景：看如下信息查询的不同</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># </span><br><span class="line"><span class="keyword">select</span> id,name,age <span class="keyword">from</span> db_test <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;zs&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> id,name <span class="keyword">from</span> db_test <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;zs&#x27;</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>首先这里涉及到了MySQL存储索引的方式，需要了解B+树的结构，还需要了解索引文件的结构</p>
<p>第一条语句执行顺序：先去name的索引文件找到“zs”，根据“zs”找到数据ID，再根据ID，去ID的索引文件（聚簇索引文件）中寻找匹配的数据，返回数据。</p>
<p>第二条语句执行顺序：去name的索引文件找到“zs”，根据“zs”找到了ID，这时已经找到了所需数据，直接返回数据。</p>
<p>上面的不同是，第二条语句没有<strong>回表</strong>，仅靠辅助索引就找到了所需的数据，这就是覆盖索引。</p>
</blockquote>
<h3 id="索引操作"><a href="#索引操作" class="headerlink" title="索引操作"></a>索引操作</h3><h4 id="建表时指定索引"><a href="#建表时指定索引" class="headerlink" title="建表时指定索引"></a>建表时指定索引</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"># 建表时添加主键索引</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable(  </span><br><span class="line">	`ID` <span class="type">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,   </span><br><span class="line">	`username` <span class="type">VARCHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,  </span><br><span class="line">	<span class="keyword">PRIMARY</span> KEY (`ID`));</span><br><span class="line">	</span><br><span class="line"># 建表时添加普通索引</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable(  </span><br><span class="line">	`ID` <span class="type">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,   </span><br><span class="line">	`username` <span class="type">VARCHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,  </span><br><span class="line">	INDEX [indexName] (username(length)));</span><br><span class="line"></span><br><span class="line"># 建表时指定唯一索引</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable(  </span><br><span class="line">	`ID` <span class="type">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,   </span><br><span class="line">	`username` <span class="type">VARCHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,  </span><br><span class="line">	<span class="keyword">UNIQUE</span> [indexName] (username(length)));</span><br><span class="line">	</span><br><span class="line"># 建表时添加组合索引</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable(  </span><br><span class="line">	`ID` <span class="type">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,   </span><br><span class="line">	`username` <span class="type">VARCHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `userage` <span class="type">VARCHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">	INDEX <span class="operator">&lt;</span>indexName<span class="operator">&gt;</span> (username,userage);</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114154335.png" alt="image-20220114154333970"></p>
<h4 id="添加索引"><a href="#添加索引" class="headerlink" title="添加索引"></a>添加索引</h4><blockquote>
<p>表构建完成，最好在添加数据前就想好如何建立索引。</p>
<p>添加索引有两种方式</p>
<ul>
<li>ALTER TABLE table_name ADD INDEX …  </li>
<li>CREATE INDEX … ON table_name ，（推荐，相对修改表结构更快）</li>
</ul>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"># 修改表结构,添加普通索引</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">ADD</span> INDEX <span class="operator">&lt;</span>index_name<span class="operator">&gt;</span>(column_name);</span><br><span class="line"></span><br><span class="line"># 修改表结构,添加唯一索引</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">ADD</span> <span class="keyword">UNIQUE</span> <span class="operator">&lt;</span>index_name<span class="operator">&gt;</span>(column_name);</span><br><span class="line"></span><br><span class="line"># 修改表结构，添加全文索引</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">ADD</span> FULLTEXT <span class="operator">&lt;</span>index_name<span class="operator">&gt;</span>(column_name);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"># 添加普通索引，最后这个括号必须要加,</span><br><span class="line"><span class="keyword">CREATE</span> INDEX <span class="operator">&lt;</span>indexName<span class="operator">&gt;</span> <span class="keyword">ON</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> (column_name);</span><br><span class="line"></span><br><span class="line"># 添加唯一索引</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">UNIQUE</span> INDEX <span class="operator">&lt;</span>indexName<span class="operator">&gt;</span> <span class="keyword">ON</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> (column_name);</span><br><span class="line"></span><br><span class="line"># 添加全文索引</span><br><span class="line"><span class="keyword">CREATE</span> FULLTEXT INDEX <span class="operator">&lt;</span>indexName<span class="operator">&gt;</span> <span class="keyword">ON</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> (column_name);</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114152544.png" alt="image-20220114152542931"></p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114152201.png" alt="image-20220114152200727"></p>
<h4 id="查看索引"><a href="#查看索引" class="headerlink" title="查看索引"></a>查看索引</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> INDEX <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114155321.png" alt="image-20220114155321756"></p>
<blockquote>
<p><strong>Table:</strong> 表名</p>
<p><strong>Non_unique:</strong> 是否为唯一索引，0为是，1为不是。</p>
<p><strong>Key_name 索引名称</strong>，如果名字相同则表明是同一个索引，而并不是重复，比如上图中的第四、五条数据，索引名称都是<strong>name</strong>，其实是一个联合索引。</p>
<p><strong>Seq_in_index</strong> 索引中的列序列号，从1开始。上图中的四、五条数据，<strong>Seq_in_index</strong>一个是1一个是2，就是表明在联合索引中的顺序，我们就能推断出联合索引中索引的前后顺序。</p>
<p><strong>Column_name</strong> 索引的列名。</p>
<p><strong>Collation</strong>指的是列以什么方式存储在索引中，大概意思就是字符序。</p>
<p><strong>Cardinality</strong> 是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据<strong>Cardinality</strong>来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。</p>
<p><strong>Sub_part</strong> 前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。</p>
<p><strong>Packed</strong> 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。</p>
<p><strong>Null</strong> 如果列含有NULL，则含有YES。</p>
<p><strong>Index_type</strong>表示索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE。</p>
<p><strong>Comment Index_comment</strong> 注释的意思。</p>
</blockquote>
<h4 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h4><p>同添加索引，一样有两种方式删除索引</p>
<ul>
<li>DROP，直接删除索引 （推荐，相比修改表结构更快）</li>
<li>ALTER，修改表结构</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 直接删除,如果不写的话，默认删除全部</span><br><span class="line"><span class="keyword">DROP</span> INDEX [index_name] <span class="keyword">ON</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span></span><br><span class="line"></span><br><span class="line"># 修改表结构</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">DROP</span> INDEX <span class="operator">&lt;</span>index_name<span class="operator">&gt;</span></span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114155433.png" alt="image-20220114155433349"></p>
<h2 id="数据库引擎"><a href="#数据库引擎" class="headerlink" title="数据库引擎"></a>数据库引擎</h2><h3 id="MyISAM"><a href="#MyISAM" class="headerlink" title="MyISAM"></a>MyISAM</h3><blockquote>
</blockquote>
<h3 id="InnoDB"><a href="#InnoDB" class="headerlink" title="InnoDB"></a>InnoDB</h3><blockquote>
</blockquote>
<h4 id="可以存多少数据"><a href="#可以存多少数据" class="headerlink" title="可以存多少数据"></a>可以存多少数据</h4><p>想要知道这个，我们就需要知道</p>
<ul>
<li>计算机中，磁盘存储数据的最小单元是扇区，一个扇区占512字节。</li>
<li>文件系统中，最小单位是块，一个块大小就是4K。</li>
<li>InnoDB存储引擎最小存储单元是页，一页大小是4块，即16K。所以InnoDB读取一次将会读取16K的内容。</li>
</ul>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220121110741.png" alt="20220121110741275"></p>
<p>生产环境中，B+树得高度在3-4层，叶子节点在最后一层，则有2-3层为索引层。</p>
<p>再结合B+树中，叶子节点存储数据，非叶子节点存储键值-指针，即索引-指针（注意这里考虑的是主键索引，因为只有主键索引的B+树才会存储数据）。</p>
<p>假设索引为ID，long类型（8字节），指针在InnoDB中一个指针大小为8字节（实际上是6字节），所以在非叶子节点中一条数据就是8+8=16字节。那么总共能存储的数据条数为：</p>
<p>第一层存储的索引节点 x 第二层存储的索引节点 x 第三层存储的索引节点，即<br>$$<br>[(16<em>1024) / 16] * [(16</em>1024)/16] = 1048576<br>$$<br>或<br>$$<br>[(16<em>1024) / 16] * [(16</em>1024) / 16] * [(16*1024) / 16]  = 1073741824<br>$$<br>假设每条数据占1K字节，那么一个叶子节点可以存16K/1K = 16条数据</p>
<p>所以能存放的数据量为：<u>1048567 * 16 = 16777216</u> 条数据，即三层就能实现千万级（16G）存储。</p>
<p>当然这里也需要说明一下，这是因为主键声明的较小，如果主键索引数据量*10，那么级别就会降低为十万级。</p>
<h2 id="正则表达式"><a href="#正则表达式" class="headerlink" title="正则表达式"></a>正则表达式</h2><blockquote>
<p>MySQL中使用REGEXP操作符来进行正则表达式匹配。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="center">模式</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">^</td>
<td align="left">匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。</td>
</tr>
<tr>
<td align="center">$</td>
<td align="left">匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性， 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。</td>
</tr>
<tr>
<td align="center">.</td>
<td align="left">匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。</td>
</tr>
<tr>
<td align="center">[…]</td>
<td align="left">字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。</td>
</tr>
<tr>
<td align="center">[^…]</td>
<td align="left">负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。</td>
</tr>
<tr>
<td align="center">p1|p2|p3</td>
<td align="left">匹配 p1 或 p2 或 p3。例如，’z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。</td>
</tr>
<tr>
<td align="center">*</td>
<td align="left">匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于[0，∞）个’o’。</td>
</tr>
<tr>
<td align="center">+</td>
<td align="left">匹配前面的子表达式一或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。</td>
</tr>
<tr>
<td align="center">{n}</td>
<td align="left">n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。</td>
</tr>
<tr>
<td align="center">{n,m}</td>
<td align="left">m 和 n 均为非负整数，其中n &lt;= m。最少匹配 n 次且最多匹配 m 次。</td>
</tr>
</tbody></table>
<h2 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h2><p>MySQL 事务(<strong>Transaction</strong>)主要用于处理操作量大，复杂度高的数据。</p>
<p>比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！</p>
<blockquote>
<ul>
<li>在 <code>MySQL</code> 中只有使用了 <code>Innodb</code> 数据库引擎的数据库或表才支持事务。MyISAM不支持事务。</li>
<li>事务处理可以用来维护数据库的<strong>完整性</strong>，保证成批的 <code>SQL</code> 语句要么全部执行，要么全部不执行。</li>
<li>事务用来管理 <code>insert</code>,<code>update</code>,<code>delete</code> 语句</li>
</ul>
</blockquote>
<h3 id="MVCC"><a href="#MVCC" class="headerlink" title="MVCC"></a>MVCC</h3><blockquote>
<p>全称是<strong>M</strong>ulti<strong>V</strong>ersion <strong>C</strong>oncurrency <strong>C</strong>ontrol ，翻译过来就是多版本并发控制，一般用于数据库管理系统，实现对数据库的并发访问。</p>
</blockquote>
<h4 id="为什么需要"><a href="#为什么需要" class="headerlink" title="为什么需要"></a>为什么需要</h4><blockquote>
<p>数据库通常使用锁来实现隔离性。最原生的锁，锁住该资源后会禁止其他任何线程访问该资源。</p>
<p>矛盾产生：大多数的业务场景是读多写少，而读锁之间的相互排斥就会显得不是很必要。</p>
<p>所以产生了读锁、写锁。读锁与读锁之间不互斥，写锁与读锁或写锁互斥。</p>
<p>之后由于业务场景再次升级，仅仅是读锁、写锁已经满足不了当时的业务场景，所以有了读锁与写锁不互斥的方法，就是在读取数据时将数据按照<strong>快照</strong>的方式保存下来，即<code>MVCC</code>，这样读锁与写锁不在冲突。</p>
<p><strong>快照</strong>在不同的数据库有不同的实现方式。</p>
</blockquote>
<h4 id="InnoDB-1"><a href="#InnoDB-1" class="headerlink" title="InnoDB"></a>InnoDB</h4><blockquote>
<p>上面说到，在<code>MySQL</code>中只有<code>InnoDB</code>支持事务，而在事务的四大隔离级别中又只有<code>READ COMMITTED</code> 、<code>REPEATABLE READ</code>支持<code>MVCC</code>，因为<code>READ UNCOMMITTED</code> 的业务场景不符合<code>MVCC</code>的业务场景，他只读取最新版本的数据，而<code>SERIALIZABLE</code>会对所有的读取行加锁，不存在共享。    </p>
</blockquote>
<h4 id="实现"><a href="#实现" class="headerlink" title="实现"></a>实现</h4><p>// TODO </p>
<h3 id="ACID"><a href="#ACID" class="headerlink" title="ACID"></a>ACID</h3><blockquote>
<p>事务满足四个条件,即ACID</p>
<ul>
<li>原子性(Automatic):一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成.</li>
<li>一致性(Consistency):WAL(Write-Ahead Logging)</li>
<li>隔离性(Isolation):数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别,包括读未提交,读已提交,可重复读,串行化.</li>
<li>持久性(Durablity):事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。</li>
</ul>
</blockquote>
<blockquote>
<p>在 <code>MySQL</code> 命令行的默认设置下，事务都是自动提交的，即执行 <code>SQL</code> 语句后就会马上执行 <code>COMMIT</code> 操作。因此要显式地开启一个事务务须使用命令 <code>BEGIN</code> 或 <code>START TRANSACTION</code>，或者执行命令 <code>SET AUTOCOMMIT=0</code>，用来禁止使用当前会话的自动提交。</p>
</blockquote>
<h3 id="BEGIN"><a href="#BEGIN" class="headerlink" title="BEGIN"></a>BEGIN</h3><blockquote>
<p>开始一个事务,等同于 <code>START TRANSACTION</code>.</p>
</blockquote>
<h3 id="COMMIT"><a href="#COMMIT" class="headerlink" title="COMMIT"></a>COMMIT</h3><blockquote>
<p>提交一个事务,等同于<code>COMMIT WORK</code>.</p>
</blockquote>
<h3 id="ROLLBACK"><a href="#ROLLBACK" class="headerlink" title="ROLLBACK"></a>ROLLBACK</h3><blockquote>
<p>事务回滚,等同于<code>ROLLBACK WORK</code>.</p>
<p><code>ROLLBACK TO identifier</code>,回滚到保存点.</p>
</blockquote>
<h3 id="SAVEPOINT"><a href="#SAVEPOINT" class="headerlink" title="SAVEPOINT"></a>SAVEPOINT</h3><blockquote>
<p><code>SAVEPOINT identifier</code>,在事务中创建一个保存点,在一个事务中可以有多个保存点.</p>
<p>可以利用保存点开启子事务,子事务失败进行回滚,而不影响主事务.</p>
</blockquote>
<h3 id="RELEASE"><a href="#RELEASE" class="headerlink" title="RELEASE"></a>RELEASE</h3><blockquote>
<p><code>RELEASE SAVEPOINT identifier</code> ,释放/删除一个保存点,</p>
</blockquote>
<h3 id="SET"><a href="#SET" class="headerlink" title="SET"></a>SET</h3><blockquote>
<p><code>SET TRANSACTION</code>,设置事务隔离级别.</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 查看当前隔离级别</span><br><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@tx</span>_isolation;</span><br><span class="line"></span><br><span class="line"># 在事务开始之前设置此次事务会话的隔离级别</span><br><span class="line"><span class="keyword">SET</span> SESSION TRANSACTION ISOLATION LEVEL (READ COMMITTED<span class="operator">/</span>READ UNCOMMITTED<span class="operator">/</span>REPEATABLE READ<span class="operator">/</span>SERIALIZABLE)</span><br><span class="line"></span><br><span class="line"># 设置全局事务默认的隔离级别</span><br><span class="line"><span class="keyword">SET</span> <span class="keyword">GLOBAL</span> TRANSACTION ISOLATION LEVEL (READ COMMITTED<span class="operator">/</span>READ UNCOMMITTED<span class="operator">/</span>REPEATABLE READ<span class="operator">/</span>SERIALIZABLE)</span><br></pre></td></tr></table></figure>

<h3 id="问题"><a href="#问题" class="headerlink" title="问题"></a>问题</h3><blockquote>
<p>事务在<strong>并发</strong>时可产生以下数据问题</p>
<ul>
<li><strong>脏读</strong></li>
<li><strong>不可重复读</strong></li>
<li><strong>幻读</strong></li>
<li><strong>第一类更新丢失</strong></li>
<li><strong>第二类更新丢失</strong></li>
</ul>
</blockquote>
<h4 id="脏读"><a href="#脏读" class="headerlink" title="脏读"></a>脏读</h4><blockquote>
<p>指事务读取到了另一事务<strong>未提交的数据</strong>。</p>
<p>隔离级别为读未提交可产生脏读，可重复读，幻读。</p>
</blockquote>
<p>举个:chestnut:</p>
<ul>
<li>事务A开启，进行读取数据。</li>
<li>事务B开启，修改数据，此时，事务B未提交。</li>
<li>事务A再次读取数据，读取到了B修改的数据。</li>
<li>事务B由于某种原因，进行了回滚。此时事务A读取到的数据就是脏数据，即脏读。</li>
</ul>
<h4 id="不可重复读"><a href="#不可重复读" class="headerlink" title="不可重复读"></a>不可重复读</h4><blockquote>
<p>指在一事务中，前后两次读取到的数据不一致。这里的不一致指的是某一<strong>数据的值</strong>前后发生了变化。强调的是<strong>一条或多条数据的值</strong>。</p>
</blockquote>
<p>举个:chestnut:</p>
<ul>
<li>事务A开启，进行读取数据。</li>
<li>事务B开启，修改数据，提交。</li>
<li>事务A再次读取数据，读取到了B修改的数据，导致两次读取数据产生了差异，即不可重复读。</li>
</ul>
<h4 id="幻读"><a href="#幻读" class="headerlink" title="幻读"></a>幻读</h4><blockquote>
<p>指在一事务中，前后两次读取到的数据不一样。这里的不一样指的是<strong>数据集合</strong>的增减差异。强调的是<strong>数据集合</strong>。</p>
</blockquote>
<p>举个:chestnut:</p>
<ul>
<li>事务A开启，进行读取数据。</li>
<li>事务B开启，新增数据，提交。</li>
<li>事务A再次读取数据，读取到了B新增的数据，导致两次读取到的数据集合不一样，即幻读。</li>
</ul>
<h4 id="第一类更新丢失"><a href="#第一类更新丢失" class="headerlink" title="第一类更新丢失"></a>第一类更新丢失</h4><blockquote>
<p>指两个事务都对数据进行更新，但是由于由于某种原因，其中一个事务进行了回滚，把事务B已提交的更新的数据给覆盖了。</p>
</blockquote>
<h4 id="第二类更新丢失"><a href="#第二类更新丢失" class="headerlink" title="第二类更新丢失"></a>第二类更新丢失</h4><blockquote>
<p>指两个事务都对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。</p>
</blockquote>
<hr>
<h3 id="事务隔离级别"><a href="#事务隔离级别" class="headerlink" title="事务隔离级别"></a>事务隔离级别</h3><blockquote>
<p>在<code>MySQL</code>中,<code>InnoDB</code>存储引擎提供的事务隔离级别有四种，隔离级别由低到高为</p>
<ul>
<li>读未提交,<code>READ UNCOMMITTED</code></li>
<li>读已提交,<code>READ COMMITTED</code></li>
<li>可重复读,<code>REPEATABLE READ</code></li>
<li>串行读取,<code>SERIALIZABLE</code></li>
</ul>
<p>事务的隔离级别越高，越能保证数据的一致性完整性，但是执行效率也越低。</p>
<p><code>MySQL</code>默认启用的是可重复读。</p>
<p>参考：<a target="_blank" rel="noopener" href="https://zhuanlan.zhihu.com/p/453241138%E3%80%81https://www.zhihu.com/question/458275373/answer/1873975439">https://zhuanlan.zhihu.com/p/453241138、https://www.zhihu.com/question/458275373/answer/1873975439</a></p>
</blockquote>
<h4 id="读未提交"><a href="#读未提交" class="headerlink" title="读未提交"></a>读未提交</h4><blockquote>
<p>字面意思,在事务没提交的时候仍然可以读到更新后的数据,即该级别的事务对查询无影响.</p>
<p>由此会产生问题</p>
<ul>
<li><p>不可重复读，由于可读取到未提交的数据，前后两次读取到的数据不一致，产生不可重复读。</p>
</li>
<li><p>如果事务B因为某种原因进行了回滚，那么就会导致事务A的查询读到的数据为脏数据，即脏读。</p>
</li>
<li><p>同理，如果事务B进行的是新增操作，就会产生幻读。</p>
</li>
</ul>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 开启事务前先查看数据库数据</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student;</span><br><span class="line"></span><br><span class="line"># <span class="number">1.</span>设置事务A、B为读未提交的隔离级别，需要开启两个终端，<span class="keyword">set</span> session指设置当前会话的隔离级别</span><br><span class="line"><span class="keyword">set</span> session transaction isolation level read uncommitted;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117100551.png" alt="image-20220117100549706"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># <span class="number">2.</span>开启事务A、B</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"><span class="keyword">start</span> transaction</span><br><span class="line"># 事务A查询，事务B修改数据</span><br><span class="line"># 查询</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student;</span><br><span class="line"># 更新</span><br><span class="line">update db_student <span class="keyword">set</span> stu_name <span class="operator">=</span> <span class="string">&#x27;read uncommitted&#x27;</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117101604.png" alt="image-20220117101602881"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 事务A再次查询</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student;</span><br><span class="line"># 两次查询结果值不一样，发生了不可重复读</span><br><span class="line"></span><br><span class="line"># 事务B回滚，提交</span><br><span class="line"><span class="keyword">rollback</span>;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"></span><br><span class="line"># 结束事务后查询，事务A查询到的数据此时就是脏数据，即脏读</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117102646.png" alt="image-20220117102644910"></p>
<h4 id="读已提交"><a href="#读已提交" class="headerlink" title="读已提交"></a>读已提交</h4><blockquote>
<p>读取到的数据是事务提交之后的数据,这样就不会发生数据回滚对查询的结果造成了影响.</p>
<p>但是，此时会有另外一个问题，假设事务A正在执行隔离级别为读已提交的事务，事务A还未提交,还没有执行结束，此时，事务B对事务A查询的表进行了更新并提交了事务，但事务A没读到更新的数据,这就是不可重复读.</p>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 开启事务前先查看数据库数据</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student;</span><br><span class="line"></span><br><span class="line"># <span class="number">1.</span>设置事务A、B为读已提交的隔离级别，需要开启两个终端，<span class="keyword">set</span> session指设置当前会话的隔离级别</span><br><span class="line"><span class="keyword">set</span> session transaction isolation level read committed;</span><br><span class="line"></span><br><span class="line"># 开启事务A、B</span><br><span class="line"><span class="keyword">begin</span>；</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111848.png" alt="image-20220117110639165"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 事务A查询，事务B新增<span class="operator">/</span>修改，事务A查询</span><br><span class="line"># 可以看到暂时没有发生不可重复读，既如此，脏读也不会发生</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117110920.png" alt="image-20220117110919072"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 事务B提交，事务查询</span><br><span class="line"># 可以看到，发生了不可重复读，但是没有发生脏读，</span><br><span class="line"># 如果事务B进行的是新增操作，那么就会发生幻读，</span><br><span class="line"># 所以读已提交解决了脏读的问题，但是没有解决不可重复读以及幻读的问题</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111206.png" alt="image-20220117111205449"></p>
<h4 id="可重复读"><a href="#可重复读" class="headerlink" title="可重复读"></a>可重复读</h4><blockquote>
<p>可重复读指在事务中读取数据时,读的是进入事务时的快照,而不一定是当前版本的数据,因为可重复读使用了MVCC机制,select不会更新版本号,但是insert,update,delete会更新版本号,是当前读(最新版本的数据).</p>
<p>这样避免了不可重复读与幻读的问题,但是不能解决脏读的问题.</p>
</blockquote>
<p>举个:chestnut:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 开启事务前先查看数据库数据</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> db_student;</span><br><span class="line"></span><br><span class="line"># <span class="number">1.</span>设置事务A、B为读已提交的隔离级别，需要开启两个终端，<span class="keyword">set</span> session指设置当前会话的隔离级别</span><br><span class="line"><span class="keyword">set</span> session transaction isolation level read committed;</span><br><span class="line"></span><br><span class="line"># 开启事务A、B</span><br><span class="line"><span class="keyword">begin</span>；</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117111838.png" alt="image-20220117111836985"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 事务A查询，事务B修改<span class="operator">/</span>新增</span><br><span class="line"># 事务A查询</span><br><span class="line"># 没有发生不可重复读，没有发生脏读</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117112223.png" alt="image-20220117112221976"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 事务B提交，事务A查询</span><br><span class="line"># 没有发生不可重复读，</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117112406.png" alt="image-20220117112405042"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 事务B新增，事务A更新事务B新增的数据，事务A查询</span><br><span class="line"># 出现了幻读，图片标注错误，说明可重复读并不会完全避免幻读</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117123321.png" alt="image-20220117123320471"></p>
<h4 id="串行读取"><a href="#串行读取" class="headerlink" title="串行读取"></a>串行读取</h4><blockquote>
<p>事务级别为串行化读取时，在执行事务期间，事务会串行执行，也会锁表，因此不会出现幻读，但是并发性极低。唯一的并发就是操作不同的表时，可能会出现并发。</p>
</blockquote>
<p>举个:chestnut:</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117123619.png" alt="image-20220117123618150"></p>
<p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_phantom">官方</a>文档说明，在所有的隔离级别中，幻读只有串行读取才能避免，在可重复读，读已提交，读未提交级别中都会出现。</p>
<p>所以就没必要举例子了。</p>
<p><strong>注意</strong></p>
<blockquote>
<p>事务隔离级别为读提交时,写数据会锁住相应的行.</p>
</blockquote>
<p><strong>隔离级别产生的问题</strong></p>
<table>
<thead>
<tr>
<th>隔离级别/产生的错误</th>
<th>脏读</th>
<th>不可重复读</th>
<th>幻读</th>
<th>第一类</th>
<th>第二类</th>
</tr>
</thead>
<tbody><tr>
<td>读未提交</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td></td>
<td></td>
</tr>
<tr>
<td>读已提交</td>
<td>❌</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td></td>
<td></td>
</tr>
<tr>
<td>可重复读</td>
<td>❌</td>
<td>❌</td>
<td>:heavy_check_mark:</td>
<td></td>
<td></td>
</tr>
<tr>
<td>串行化</td>
<td>❌</td>
<td>❌</td>
<td>❌</td>
<td></td>
<td></td>
</tr>
</tbody></table>
<p><strong>隔离级别解决的问题</strong></p>
<table>
<thead>
<tr>
<th>隔离级别/解决的错误</th>
<th>脏读</th>
<th>不可重复读</th>
<th>幻读</th>
<th>第一类</th>
<th>第二类</th>
</tr>
</thead>
<tbody><tr>
<td>读未提交</td>
<td>❌</td>
<td>❌</td>
<td>❌</td>
<td></td>
<td></td>
</tr>
<tr>
<td>读已提交</td>
<td>:heavy_check_mark:</td>
<td>❌</td>
<td>❌</td>
<td></td>
<td></td>
</tr>
<tr>
<td>可重复读</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td>❌</td>
<td></td>
<td></td>
</tr>
<tr>
<td>串行化</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td>:heavy_check_mark:</td>
<td></td>
<td></td>
</tr>
</tbody></table>
<h2 id="锁"><a href="#锁" class="headerlink" title="锁"></a>锁</h2><blockquote>
<p>数据库的锁经常与事务一起讨论，因为锁只有在多并发的情况下才会起作用。</p>
<p>MySQL中锁又与数据库引擎有关，InnoDB存储引擎支持行级锁，表级锁，MyISAM和MEMORY存储引擎支持表级锁。BDB存储引擎支持页面锁。也支持表级锁。</p>
<p>MySQL的行锁又分为共享锁（S锁），排他锁（X锁）。</p>
</blockquote>
<p>MySQL三种锁的特性</p>
<table>
<thead>
<tr>
<th></th>
<th>开销</th>
<th>死锁</th>
<th>粒度</th>
<th>并发性能</th>
</tr>
</thead>
<tbody><tr>
<td>表级锁</td>
<td>大，加锁快</td>
<td>不会发生死锁</td>
<td>大</td>
<td>发生冲突的概率高，并发性低</td>
</tr>
<tr>
<td>行级锁</td>
<td>小，加锁慢</td>
<td>会发生死锁</td>
<td>小</td>
<td>发生冲突的概率低，并发性高</td>
</tr>
<tr>
<td>页面锁</td>
<td>中</td>
<td>会发生死锁</td>
<td>中</td>
<td>发生冲突的概率中，并发性一般</td>
</tr>
</tbody></table>
<h3 id="MyISAM-1"><a href="#MyISAM-1" class="headerlink" title="MyISAM"></a>MyISAM</h3><blockquote>
<p>MyISAM只支持表级锁，且不支持事务，所以用的很少。</p>
</blockquote>
<h3 id="InnoDB-2"><a href="#InnoDB-2" class="headerlink" title="InnoDB"></a>InnoDB</h3><blockquote>
<p>InnoDB即支持表级锁，也支持行级锁，默认是行级锁。</p>
</blockquote>
<h4 id="共享锁"><a href="#共享锁" class="headerlink" title="共享锁"></a>共享锁</h4><blockquote>
<p>可以共享</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 会造成加S锁的情况</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>columns<span class="operator">&gt;</span> <span class="keyword">from</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> LOCK <span class="keyword">IN</span> SHARE MODE;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117172251.png" alt="image-20220117172251614"></p>
<h4 id="排他锁"><a href="#排他锁" class="headerlink" title="排他锁"></a>排他锁</h4><blockquote>
<p>不可共享</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"># 会造成加X锁的情况</span><br><span class="line"># <span class="keyword">FOR</span> UPDATE指当前读，即读取最新数据</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">&lt;</span>columns<span class="operator">&gt;</span> <span class="keyword">from</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">FOR</span> UPDATE;</span><br><span class="line"></span><br><span class="line"># 插入操作</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">values</span>(columns_value);</span><br><span class="line"></span><br><span class="line"># 更新操作</span><br><span class="line">UPDATE <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">SET</span> (<span class="keyword">column</span><span class="operator">=</span>new_value,<span class="keyword">column</span><span class="operator">=</span>new_value...);</span><br><span class="line"></span><br><span class="line"># 删除操作</span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="keyword">column</span><span class="operator">=</span><span class="keyword">value</span>;</span><br></pre></td></tr></table></figure>

<h5 id="FOR-UPDATE"><a href="#FOR-UPDATE" class="headerlink" title="FOR UPDATE"></a>FOR UPDATE</h5><p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117165543.png" alt="image-20220117165542747"></p>
<h5 id="新增"><a href="#新增" class="headerlink" title="新增"></a>新增</h5><h5 id="更新"><a href="#更新" class="headerlink" title="更新"></a>更新</h5><p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220117170933.png" alt="image-20220117170931875"></p>
<h5 id="删除"><a href="#删除" class="headerlink" title="删除"></a>删除</h5><h4 id="行锁实现"><a href="#行锁实现" class="headerlink" title="行锁实现"></a>行锁实现</h4><blockquote>
<p>InnoDB行锁是通过给索引上的索引项加锁来实现的。这一点与Oracle不同，Oracle是通过在数据块中对相应的数据行加锁来实现。</p>
<p>InnoDB这种加锁意味着只有通过索引条件检索数据，InnoDB才能使用行锁，否则InnoDB将使用表锁。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"># 查看默认事务隔离级别</span><br><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@TRANSACTION</span>_ISOLATION;</span><br><span class="line"></span><br><span class="line"># 查看表索引</span><br><span class="line"><span class="keyword">SHOW</span> INDEX <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span>;</span><br><span class="line"></span><br><span class="line"># 开启事务</span><br><span class="line"><span class="keyword">BEGIN</span>；</span><br><span class="line"></span><br><span class="line"># 事务A <span class="keyword">FOR</span> UPDATE会给ID<span class="operator">=</span><span class="number">1</span>的查询加锁，因为是当前读，此时应该是给ID<span class="operator">=</span><span class="number">1</span>的数据加行锁</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> ID <span class="operator">=</span> <span class="number">1</span> <span class="keyword">FOR</span> UPDATE;</span><br><span class="line"></span><br><span class="line"># 事务B 尝试给当前读，即获取读锁，被阻塞，如果事务A加了行锁，此时需要等待是正常的</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> ID <span class="operator">=</span> <span class="number">1</span> <span class="keyword">FOR</span> UPDATE;</span><br><span class="line"></span><br><span class="line"># 事务B 尝试给当前读，即获取读锁，被阻塞，如果事务A加了行锁，此时不需要等待，但实际上是等待，所以判断是表锁</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="operator">&lt;</span>table_name<span class="operator">&gt;</span> <span class="keyword">WHERE</span> ID <span class="operator">=</span> <span class="number">2</span> <span class="keyword">FOR</span> UPDATE;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118090647.png" alt="image-20220118090647050"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 接下来我们查看一下，加锁的锁信息</span><br><span class="line"># 在事务B尝试加锁后，我们查询当前数据库的INNODB_LOCKS，即正在锁的事务</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> INFORMATION_SCHEMA.INNODB_LOCKS;</span><br><span class="line"></span><br><span class="line"># 查看等待锁的事务</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> INFORMATION_SCHEMA.INNODB_LOCK_WAITS;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118094737.png" alt="image-20220118094737849"></p>
<blockquote>
<p><code>INFORMATION_SCHEMA.INNODB_LOCK_WAITS</code></p>
<ul>
<li><strong>requesting_trx_id</strong>：请求锁的事务ID</li>
<li><strong>requesting_trx_id</strong>：请求的锁的ID</li>
<li><strong>blocking_trx_id</strong>：阻塞的事务的ID</li>
<li><strong>blocking_lock_id</strong>:阻塞的锁的ID</li>
</ul>
<p><code>INFORMATION_SCHEMA.INNODB_LOCKS</code></p>
<ul>
<li><strong>lock_id</strong>：锁ID</li>
<li><strong>lock_trx_id</strong>：事务ID</li>
<li><strong>lock_mode</strong>：锁模式<ul>
<li><strong>S</strong>：S锁，即共享锁（Share）</li>
<li><strong>X</strong>：X锁，即排他锁（Exclusive）</li>
</ul>
</li>
<li><strong>lock_type</strong>：锁类型<ul>
<li><strong>RECORD</strong>：记录锁，锁的是索引，如果表上没有索引，那么<code>InnoDB</code>会自动创建一个隐藏的聚簇索引，此时锁住的就是这个隐藏的聚簇索引。所以当一个<code>SQL</code>语句没走索引，或该表没有索引时，将会在每个自动创建出来的聚簇索引上加<strong>X锁</strong>，间接的实现了<strong>表级锁</strong>，即锁升级 (<strong>?</strong>)。</li>
</ul>
</li>
<li><strong>lock_tabe</strong>：锁定的表</li>
<li><strong>lock_index</strong>：锁定的索引，因为<code>InnoDB</code>的锁需要命中索引，然后给索引加锁<ul>
<li><strong>GEN_CLUST_INDE</strong>：聚类索引，这个索引是由于表没有建立索引，所以在进行加锁时，需要自动建立一个索引，但是这样建立的索引会给所有的行加锁，导致<strong>行锁</strong>升级成了<strong>表锁</strong>。所以当你的<code>lock_index = GEN_CLUST_INDE</code>时，你需要查看表是否缺少索引，及时建立索引，避免表锁。</li>
</ul>
</li>
<li><strong>lock_space</strong>：锁定的空间</li>
<li><strong>lock_page</strong>：锁定的页，这里涉及到了内存</li>
<li><strong>lock_rec</strong>：</li>
<li><strong>lock_data</strong>：锁定的数据</li>
</ul>
</blockquote>
<h3 id="查看锁"><a href="#查看锁" class="headerlink" title="查看锁"></a>查看锁</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># chakan锁执行次数</span><br><span class="line"><span class="keyword">SHOW</span> STATUS <span class="keyword">LIKE</span> <span class="string">&#x27;%lock%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220118092826.png" alt="image-20220118092826558"></p>
<blockquote>
<ul>
<li>Table_locks_waited：如果该值较大，说明存在较严重的表级锁竞争</li>
</ul>
<p><code>Table_locks_immediate</code>表示立即释放表锁数，<code>Table_locks_waited</code>表示需要等待的表锁数，<br>如果<code>Table_locks_immediate / Table_locks_waited &gt; 5000</code>，最好采用<code>InnoDB</code>引擎，<br>因为<code>InnoDB</code>是行锁而<code>MyISAM</code>是表锁，对于高并发写入的应用<code>InnoDB</code>效果会好些。</p>
</blockquote>
<p>不同隔离级别涉及到的锁 参考：</p>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/xifeijian/article/details/20313977?spm=1001.2101.3001.6650.10&amp;utm_medium=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~default-10.pc_relevant_paycolumn_v2&amp;depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~default-10.pc_relevant_paycolumn_v2&amp;utm_relevant_index=16">https://blog.csdn.net/xifeijian/article/details/20313977?spm=1001.2101.3001.6650.10&amp;utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-10.pc_relevant_paycolumn_v2&amp;depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-10.pc_relevant_paycolumn_v2&amp;utm_relevant_index=16</a></p>
<hr>
<h2 id="日志"><a href="#日志" class="headerlink" title="日志"></a>日志</h2><blockquote>
<p>日志是MySQL数据库的重要组成部分，记录着数据库运行期间各种状态信息。</p>
<p>MySQL日志主要包括以下日志</p>
<ul>
<li><p>错误日志、</p>
</li>
<li><p>查询日志、</p>
</li>
<li><p>慢查询日志、</p>
</li>
<li><p>事务日志（包括<code>redo log</code>和<code>undo log</code>）、</p>
</li>
<li><p>二进制日志（<code>bin log</code>）</p>
</li>
</ul>
<p>我们需要了解的目前就是<code>redo log</code>、<code>undo log</code> 、<code>bin log</code>。</p>
<p>参考：<a target="_blank" rel="noopener" href="https://zhuanlan.zhihu.com/p/190886874%E3%80%81https://zhuanlan.zhihu.com/p/343043260">https://zhuanlan.zhihu.com/p/190886874、https://zhuanlan.zhihu.com/p/343043260</a></p>
</blockquote>
<h3 id="BINLOG"><a href="#BINLOG" class="headerlink" title="BINLOG"></a>BINLOG</h3><hr>
<h2 id="优化"><a href="#优化" class="headerlink" title="优化"></a>优化</h2><blockquote>
<p>说到优化，大家一直想到的就是加索引，但是加索引真的能优化查询嘛？加了索引你就一定能够让你的SQL语句命中索引吗？加了索引但是不能命中索引，那不是相当于索引没起作用，所以加了索引真的不一定能够提高查询效率。</p>
<p>可以参考：<a target="_blank" rel="noopener" href="https://baijiahao.baidu.com/s?id=1669796110955401759&amp;wfr=spider&amp;for=pc">https://baijiahao.baidu.com/s?id=1669796110955401759&amp;wfr=spider&amp;for=pc</a></p>
</blockquote>
<p><strong>优先级</strong></p>
<blockquote>
<p>其实优化是分优先级的，比如软件级别的优化（调整SQL语句、添加索引、分库分表等）与硬件级别的优化（添加服务器），看不同的场景选择合适的优化方案。</p>
<p>场景一：618限时活动，服务器压力过大，在这种时间有限的且不建议重启服务的场景下就适合使用硬件优化-添加服务器。</p>
<p>场景二：系统存在慢查询，且此服务是长期服务，那么此时我们就需要考虑优化SQL。</p>
</blockquote>
<p><strong>优化思路</strong></p>
<blockquote>
<p>对于优化，我们要有一个宏观的概念，我们需要知道在系统中哪些服务最容易达到上限。</p>
<ol>
<li>一般来说是数据库先达到瓶颈，所以我们需要先排查数据库的问题。<ul>
<li>索引</li>
<li>缓存</li>
<li>分库分表</li>
</ul>
</li>
<li>根据业务场景考虑是扩容还是优化代码<ul>
<li>纵向：提升单体的能力</li>
<li>横向：提升服务的数量</li>
<li>优化代码，比如异步通信，队列，缓存等</li>
</ul>
</li>
<li>JVM级优化<ul>
<li>GC</li>
<li>调参</li>
</ul>
</li>
<li>网络和操作系统<ul>
<li>网络是否对系统存在限制</li>
<li>操作系统是否对系统存在限制</li>
</ul>
</li>
</ol>
</blockquote>
<p>先从宏观看一下数据库查询的过程</p>
<p>数据库将数据保存在磁盘，那么查询数据就是读取磁盘，一次磁盘IO需要经历寻道，旋转延时，数据传输。<br>$$<br>单次IO时间= 寻道时间+旋转延时+数据传输时间<br>$$</p>
<h3 id="正确的创建索引"><a href="#正确的创建索引" class="headerlink" title="正确的创建索引"></a>正确的创建索引</h3><h4 id="尽量使用自增主键"><a href="#尽量使用自增主键" class="headerlink" title="尽量使用自增主键"></a>尽量使用自增主键</h4><h4 id="选择性高的列优先"><a href="#选择性高的列优先" class="headerlink" title="选择性高的列优先"></a>选择性高的列优先</h4><h4 id="考虑索引的大小"><a href="#考虑索引的大小" class="headerlink" title="考虑索引的大小"></a>考虑索引的大小</h4><h4 id="联合索引优于独立索引"><a href="#联合索引优于独立索引" class="headerlink" title="联合索引优于独立索引"></a>联合索引优于独立索引</h4><h4 id="覆盖索引避免回表"><a href="#覆盖索引避免回表" class="headerlink" title="覆盖索引避免回表"></a>覆盖索引避免回表</h4><hr>
<h3 id="注重查看执行计划"><a href="#注重查看执行计划" class="headerlink" title="注重查看执行计划"></a>注重查看执行计划</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 建议所有的<span class="keyword">sql</span>语句都走一遍执行计划，了解他是否命中了索引，是否还有优化的空间</span><br><span class="line">EXPLAIN <span class="operator">&lt;</span><span class="keyword">SQL</span><span class="operator">&gt;</span>;</span><br></pre></td></tr></table></figure>

<hr>
<h3 id="千万级数据库优化"><a href="#千万级数据库优化" class="headerlink" title="千万级数据库优化"></a>千万级数据库优化</h3><blockquote>
<p>对MySQL来说，千万级的数据量</p>
<p>参考：<a target="_blank" rel="noopener" href="https://baijiahao.baidu.com/s?id=1699806296118060495">https://baijiahao.baidu.com/s?id=1699806296118060495</a></p>
</blockquote>
<hr>
<h2 id="错误"><a href="#错误" class="headerlink" title="错误"></a>错误</h2><p>在我们写<code>SQL</code>时经常遇到奇奇怪怪的错误类型,如果我们不加以总结,每次遇到这个错误都是感觉是新的错误,这是一种很糟糕的学习历程.所以我准备将遇到的错误加以总结,以后遇到这些问题能够在这里找到答案.</p>
<h3 id="syntax"><a href="#syntax" class="headerlink" title="syntax"></a>syntax</h3><blockquote>
<p>语法错误.</p>
</blockquote>
<h5 id="ERROR-1064"><a href="#ERROR-1064" class="headerlink" title="ERROR 1064"></a>ERROR 1064</h5><ul>
<li>大概率是因为<code>SQL</code>语句缺了点什么,不符合<code>MySQL</code>标准</li>
<li>有可能是<code>MySQL</code>不支持该语法.</li>
</ul>
<p><strong>错误示例1064</strong>:缺少数值类型</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114111651.png" alt="image-20220114111650352"></p>
<p><strong>错误示例1064</strong>:不支持语法类型</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114122552.png" alt="image-20220114122551035"></p>
<h5 id="ERROR-1091-1054"><a href="#ERROR-1091-1054" class="headerlink" title="ERROR 1091/1054"></a>ERROR 1091/1054</h5><p>大概率是拼写出了错,他找不到对应的值.</p>
<p><strong>错误示例1091</strong>:单词拼写错误</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114112031.png" alt="image-20220114112029772"></p>
<p><strong>错误示例1054</strong>:单词拼写错误</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114114751.png" alt="image-20220114114749932"></p>
<h5 id="ERROR-1046"><a href="#ERROR-1046" class="headerlink" title="ERROR 1046"></a>ERROR 1046</h5><p>没选择数据库,需要选择一个数据库.</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115103.png" alt="image-20220114115101984"></p>
<h5 id="ERROR-1045"><a href="#ERROR-1045" class="headerlink" title="ERROR 1045"></a>ERROR 1045</h5><p>密码输入错误.</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115149.png" alt="image-20220114115148284"></p>
<h5 id="ERROR-1221"><a href="#ERROR-1221" class="headerlink" title="ERROR 1221"></a>ERROR 1221</h5><p>错误使用<code>UNION</code>和<code>ORDER BY</code>.</p>
<p><img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20220114115319.png" alt="image-20220114115318582"></p>
<h5 id="ERROR-1241"><a href="#ERROR-1241" class="headerlink" title="ERROR 1241"></a>ERROR 1241</h5><p>Operand should contain 1 column(s)，这是因为select语句后面不应该加括号，而是直接跟列明。</p>
<h5 id="ERROR-1273"><a href="#ERROR-1273" class="headerlink" title="ERROR 1273"></a>ERROR 1273</h5><p>Unknown collation: ‘utf8mb4_0900_ai_ci’.这是由于MySQL5.0和MySQL8.0字符集默认字符集不一致导致的。</p>
<p>在MySQL5.*中支持<code>utf8mb4_general_ci</code>字符集，在MySQL8.*中支持<code>utf8mb4_0900_ai_ci</code>。</p>

      
    </div><!-- .entry-content -->

    <footer class="entry-meta">
    <a href="/2021/06/01/Mysql/">
    <time datetime="2021-06-01T00:00:00.000Z" class="entry-date">
        2021-06-01
    </time>
</a>
    
    
    </footer>
</article>


    
<nav class="nav-single">
    <h3 class="assistive-text">文章导航</h3>
    
        <span class="nav-previous"><a href="/2021/07/01/Android/" rel="prev"><span class="meta-nav">←</span> Android</a></span>
    
    
        <span class="nav-next"><a href="/2021/05/01/Spring/" rel="next">Spring Boot <span class="meta-nav">→</span></a></span>
    
</nav><!-- .nav-single -->






<section id="comments">
  <!-- 多说评论框 start -->
  <div class="ds-thread" data-thread-key="post-Mysql" data-title="MySQL" data-url="https://masuo777.github.io/2021/06/01/Mysql/"></div>
  <!-- 多说评论框 end -->
  <!-- 多说公共JS代码 start (一个网页只需插入一次) -->
  <script type="text/javascript">
  var duoshuoQuery = {short_name:'ms'};
    (function() {
	  var ds = document.createElement('script');
	  ds.type = 'text/javascript';ds.async = true;
	  ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
	  ds.charset = 'UTF-8';
	  (document.getElementsByTagName('head')[0]
	   || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>
  <!-- 多说公共JS代码 end -->
</section>


</div></div>
        <div id="secondary" class="widget-area" role="complementary">
  
    <aside class="widget">
    <h3 class="widget-title">CONTACT ME</h3>
    <div class="widget-content">
      <p>邮箱：masuo0.0@foxmail.com</p>
      <p>QQ：2919029885</p>
      <a target="_blank" rel="noopener" href="https://www.cnblogs.com/msdog/">博客园</a>
      </br>
      <img src="https://masuo-github-image.oss-cn-beijing.aliyuncs.com/image/20210701223301.jpg" height="250px"/>
      
    </div>
</aside>
  
    <aside class="widget">
    <h3 class="widget-title">赞助我</h3>
    <div class="widget-content">
      <img src='/css/images/alipay.png' style='width:100%;' />
    </div>
</aside>
  
    <aside class="widget">
    <h3 class="widget-title">公众号</h3>
    <div class="widget-content">
      <p>暂时没有，待我代码行数100000，我可能才有这个资格吧。</p>
      <p></p>
    </div>
</aside>
  
</div>
      </div>
      <footer id="colophon" role="contentinfo">
    <p>&copy; 2022 John Doe; All rights reserved.</p>
    <p>Powered by <a href="http://hexo.io/" target="_blank">Hexo</a></p>
</footer>
    <script>window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"1","bdMiniList":false,"bdPic":"","bdStyle":"2","bdSize":"16"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];</script>

<script src="/js/jquery-2.0.3.min.js"></script>


  
<link rel="stylesheet" href="/fancybox/jquery.fancybox.css">

  
<script src="/fancybox/jquery.fancybox.pack.js"></script>




<script src="/js/script.js"></script>


<script src="/js/navigation.js"></script>

<div id="bg"></div>

  </div>
</body>
</html>